indirect function

[Thomas Roos]

I have the following formula which is giving me the #REF! error.
If I make the last reference a label it works but by making it a real
reference the formula dies. What am I doing wrong.
Thanks

=INDIRECT("'"&$a8&"'"!"&D$89) doesn't work
=INDIRECT("'"&$a8&"'"!"&"D$89") works but now I can't copy the formula to
use relative addresses.

=indirect(("'"&$a8&"'"!" this part works and is referencing a sheet within
a work
&D$89 this part does not. I am trying to reference a cell location in the
referenced sheet )
I want to be able to copy the formula to give me the relative reference in
the other sheet.

 

[Gary''s Student]

Perhaps something like:

=INDIRECT("'" & $A8 & "'!" & "D$" & ROWS($A$1:A1)+88)

This can be copied downward

 

[Don Guillett]

try
=INDIRECT($a8&"!$h1")

 

[muddan madhu]

try this

=INDIRECT(""&$A8&"!"&D$89)

 

[Thomas Roos]

It won't copy as relative value to the right. Any other ideas?

 

[Thomas Roos]

I want to be able to copy to the right.
What would be the answer?
Thanks

 

[Don Guillett]

Use this where a1 is the row and column(i:i) is the column. You can change
absolutes and starting area to suit.

INDIRECT($O11&"!"&ADDRESS(ROW($A1),COLUMN(I:I)))