MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Cell reference within a sheet reference?!?!?!


Posted by Dustin on March 05, 2001 11:59 AM

Is it possible to use a cell reference within a sheet reference. I have a large sheet that contains almost one hundred sections of three repeating lines. Each line contains a sheet reference to another worksheet. I can save a bunch of time doing "find/replace" if I can figure out a way to use a cell reference (the cell referenced would contain the actual page name to be used in the sheet reference).
Any ideas?!?!?
Thanks very much.


Posted by Dave Hawley on March 05, 2001 12:32 PM

Hi Dustin

Not too sure I understand you, but if cell A1 had the text "Sheet3" in it, then this formula would reference Cell A10 on Sheet3.

=INDIRECT(A1 & "!A10")


Dave
OzGrid Business Applications

Posted by Dustin on March 05, 2001 1:05 PM

Re: My actual scenario


Thanks for responding Dave. Nice to speak with you again. Hopefully the additional information will clear it up a bit.

Here is my actual scenario:
A B
1 Arizona =IF(ISERR(Arizona!G$8/Arizona!G$7),"",Arizona!G$8/Arizona!G$7)

2 Florida =IF(ISERR(Florida!G$8/Florida!G$7),"",Florida!G$8/Florida!G$7)

3 Texas =IF(ISERR(Texas!G$8/Texas!G$7),"",Texas!G$8/Texas!G$7)

I am trying to figure out if I can use a cell reference within a sheet reference (in column B) instead of writing out the name of the sheet (Arizona, Florida, Texas, etc - value in column A) so that I can make use of the relative cell reference when I copy the formula instead of having to rewrite or find/replace it for each section.

Similar to the following (obviously my syntax is incorrect):
A B
1 Arizona =IF(ISERR($A1)!G$8/($A1)!G$7),"",($A1)!G$8/($A1)!G$7)

2 Florida =IF(ISERR($A2)!G$8/($A2)!G$7),"",($A2)!G$8/($A2)!G$7)

3 Texas =IF(ISERR($A3)!G$8/($A3)!G$7),"",($A3)!G$8/($A3)!G$7)


Posted by Dave Hawley on March 05, 2001 1:25 PM

Re: My actual scenario


Dustin, in this case this will work.

=IF(ISERR(INDIRECT(ADDRESS(8,7,3,,A1))/INDIRECT(ADDRESS(7,7,3,,A1))),"",INDIRECT(ADDRESS(8,7,3,,A1))/INDIRECT(ADDRESS(7,7,3,,A1)))

Dave


OzGrid Business Applications

Posted by Dave Hawley on March 05, 2001 1:30 PM

Use this one!

=IF(ISERR(INDIRECT(ADDRESS(8,7,2,,$A1))/INDIRECT(ADDRESS(7,7,2,,$A1))),"",INDIRECT(ADDRESS(8,7,2,,$A1))/INDIRECT(ADDRESS(7,7,2,,$A1)))

Dave


OzGrid Business Applications

Posted by Dustin on March 05, 2001 1:38 PM

Thanks Dave. I'll let you know how they work...

Dustin, in this case this will work. =IF(ISERR(INDIRECT(ADDRESS(8,7,3,,A1))/INDIRECT(ADDRESS(7,7,3,,A1))),"",INDIRECT(ADDRESS(8,7,3,,A1))/INDIRECT(ADDRESS(7,7,3,,A1)))