How do I set a formula, that add next cell reference in a Formula

[Gary]

I am populating value from another worksheet in form format using following
formula in a cell, let us say in A5 of current worksheet.

=Data!E1

Now I want to have contents of worksheet "Data", cell E2 in cell A10 of
current worksheet.

Like that I want to keep using the incremental values for N number of times.
How can I do that?



=

 

[Ken]

Gary

I'm not sure exactly what you are trying to do, but, I believe you
will need to build your reference to the data worksheet in an INDIRECT
formula.

This formula in cell A10 of sheet2 will get the value from cell E2 of
your data worksheet.

=INDIRECT("data!e"&ROW()/10+1)

I am not sure why this would be better than simply putting the formula
=Data!E1 in cell A10 since you still don't have anything you an copy
down but maybe it will get you started. The MOD function inside the
INDIRECT funcion is often helpful as well when non contiguous but
periodic cells on one sheet reference contiguous areas on another
sheet.

Good luck.

Ken
Norfolk, Va

 

[Shane Devenshire]

Hi,

1. In A5 enter the formula

=INDIRECT("Data!E"&ROW(A5)/5)

2. Highlight A5:A9 and drag the fill handle down as far as necessary.