Excel formulas 'following the data'

[JesOakley]

Probably a stoopid question, but I can't seem to find the answer anywhere...
I have a column of data values (numbers) in column A, and a cumulative total going in column B, using

=if(a3<>"",a3+b2,b2)

in B3, kinda thing.
My problem is that if I drag the number value from A3 out of the column e.g. to D6, then the formula in B3 'follows' the data, changing it to

=if(d6<>"",d6+b2,b2)

, and the cumulative total remaining unchanged. What I want is for B3 to remain focused on the data in A3, no matter what is dragged into/out of A3, and for the cumulative total to increase/decrease as appropriate.
I have tried absolute references, and protecting cells, but that didn't work. Tried in Excel 2003 & 2007.
Anybody out there any ideas?

 

[AnotherHiggins]

[tab]=Indirect("A1")+Indirect("A2")

But why would dragging cells around be part of the regular use of the sheet?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GlennUK]

Something like:

=IF(A3<>"",OFFSET(B3,0,-1,1,1)+B2,B2)

or maybe simply

=OFFSET(B3,0,-1,1,1)+B2

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[JesOakley]

Thanks to both of you guys for simple, working solutions that were rapidly provided!
I think I prefer the offset method, because that still allows you to copy/paste your formula into the full column without intervention. Because the indirect method doesn't change the text values of the formula (which, when all's said and done was the point of using it) it needs all the references to be changed manually. Nevertheless I appreciate both of your inputs. Cheers.

 

[GlennUK]

Glad to help. By the way you can combine the ROW function with INDIRECT to get a formula that you can copy, like:

=INDIRECT("A"&ROW())+INDIRECT("B"&ROW()-1)

Cheers, Glenn.

Beauty is in the eye of the beerholder.