Referencing worksheets in formulas

[Carolyn]

I am using this formula
=IF('3.3RHS'!$A$29=0," ",'3.3RHS'!$A$29)
I would like to copy this formula using the fill handle
and have it reference the worksheets following 3.3RHS.

How do I go about doing this?

 

[Vasant Nanavati]

It can't be done by dragging and filling.

 

[Carolyn]

Not very helpful but thanks anyway I guess.
I have broken down the formula and am using concatenate
function to rebuild it, ie
if('
Sheet 2
$G$13
=
0
," "
,'
)

listed in a coloumn, which then means all I have to do is
copy over the 'sheet 2' reference and it will auto
generate the next set of formulas for me to use. Any
comment or improved methods would be appriciated

 

[Vasant Nanavati]

If you do want to go to all that trouble, you can simply list all the sheet
names in a column (say Z) in order (as long as you are not adding or
deleting sheets from time to time. Then you can use something like:

=IF(INDIRECT("'"&Z1&"'!$A$29")=0,"",INDIRECT("'"&Z1&"'!$A$29"))

and drag down. With this format you may get strange results if you
subsequently insert or delete rows.

Hope this is more helpful!

 

[Don]

Hi Carolyn,

Not sure if this will work for you or not but you might
give it a look....

Group all your sheets together and enter your
formula...that'll put your formula on every page. Then go
to each page and change the reference sheet. Might be
easier than what you laid out there.

Be sure to ungroup the pages before making any changes to
your formula.

HTH,

Don

 

[Paul Lautman]

How's about John Walkenbach's sheet offset UDF at
http://www.j-walk.com/ss/excel/tips/tip63.htm
This can easily be changed to refer to absolute sheet numbers.

But also see Jim Rech's non-UDF method at
http://groups.google.co.uk/[email protected]
(or http://tinyurl.com/4lyoz)