How do I use a formula to give me a cell reference in excel?

[Guest]

I want to be able to copy the following formula sequence down an excel sheet:
='By Name'!A2
='By Name'!A5
='By Name'!A8
....
But excel will not recognise the sequence. Can I
a) Do something so it will recognise it and copy it
or
b) Make the cell references the result of a formula e.g. ='By Name'!A(B1),
where B1 contains the reference I need.

 

[David Biddulph]

=OFFSET('By Name'!A$2,3*(ROW()-2),0) if your first formula is in row 2,
otherwise ...3*(ROW()-x)... if the first formula is in row x.

If you want to try your second approach look up the INDIRECT function in
Excel help.

 

[Guest]

This should do what you request if you start in A1 and copy down:
=INDIRECT("Name!A" & (ROW()*3)-1)

 

[Guest]

One way ..

Put this in the starting cell:
=INDIRECT("'By Name'!A"&ROW(A1)*3-1)
Copy down as far as required

The above will return the required results, ie same as:

='By Name'!A2
='By Name'!A5
='By Name'!A8

etc

---

 

[Stan Brown]

Mon, 2 Apr 2007 03:08:02 -0700 from CJWilson

I want to be able to copy the following formula sequence down an excel sheet:
='By Name'!A2
='By Name'!A5
='By Name'!A8
...
But excel will not recognise the sequence. Can I
a) Do something so it will recognise it and copy it
b) Make the cell references the result of a formula e.g. ='By Name'!A(B1),
where B1 contains the reference I need.

=INDIRECT
or =OFFSET