Prevent a cell value from changing

Question

I am importing a data table from the web which contains updated values each week for a period of 15 weeks and I am trying to build a table that tracks and stores the weekly differences

lets say the imported data I need is placed in column B when imported (this value is always a current total)

week 1 value equals value in B upon first download and is placed in column C:   (C=B)

week 2 value equals refreshed value in B minus value in C and is placed in column D:   (D=(B-C))

week 3 value equals refreshed value in B minus value in C plus value in D and is placed in E:   (E=(B-(C+D)))

and so on....

have been using a vlookup to find value in value of B in the above simplified formulas. the trouble is the value of B will change every week so the values of the formulas change each time B refreshes in a weekly download.

does anyone have any idea how to get around this.

I did come up with a clumsy solution:

cell W1 updates weekly from a value of 1 to 15 when I refresh my data then I used an if statement in my formulas:

ex. for week 1- I put this formula in C:  =if(w1=1,c=b,c)

this does prevent the value from changing if its not week 1(w1=1) but it creates a circular reference-not sure how big a deal that is?

thanks

Answer 1

Not sure if that can be done without vb codes, you are better off checking in the technet.

http://social.technet.microsoft.com/Forums/en-US/excel/threads

They should be able to guide you.

Answer 2

thanks, looking into a vba solution