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

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.

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

=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.
--
David Biddulph

"CJWilson" wrote in message
...
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.

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

This should do what you request if you start in A1 and copy down:
=INDIRECT("Name!A" & (ROW()*3)-1)
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"CJWilson" wrote:

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.

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"CJWilson" wrote:
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.

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

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

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/