# 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

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.

Dave

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

Use this one!