Naming a column and extracting values from it

[luv2glyd]

Let's say I've highlited a column of cells A1:A10 and named it "data" the same way you'd name a cell (by going to the upper left-hand corner, typing in "data" and hitting enter). Now what I can do by going to cell B1, for example, is type in $data*2 and have it output the value of cell A1 multiplied by 2.

How would I still be able to use the name "data" but in cell B1 reffer both to cell A1 (this is done automatically as illustrated above) and cell A2, but without actually having to use a specific reference to cell A2 in the formula? That is, I want to be able to have a formula like this in cell B1:

= $data*2+$data(lower by one cell of data)*2
Thus, this formula in B1 would be equivalent to:
= A1*2+A2*2

By the way, I'm using windows vista with the latest excel version on it (one with new funky commands up at the top)

Thank you.

[rylo]

HI

How about

=SUM(OFFSET(data,0,0,2,1))*2

rylo

[luv2glyd]

That seems to work but only in the first cell (B1). If I copy the same fromula down to B2, B3, B4, and so on, the values are different vs. conventional formula. How do I adjust the formula you gave me to account for this?

[rylo]

Hi

OK, try this

=SUM(OFFSET(data,ROW()-1,0,2,1))*2


rylo