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

C

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
or
b) Make the cell references the result of a formula e.g. ='By Name'!A(B1),
where B1 contains the reference I need.
 
D

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.
 
J

John Bundy

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

Max

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
Click to expand...
etc
 
S

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.
Click to expand...

=INDIRECT
or =OFFSET
 
Top