Use of Indirect

[RonM]

I am trying to do something similar to what I have seen on one of
these discussions. I have sheetnames of the other worksheets in a
workbook in row 1 of a worksheet and am wanting to refer to a formula
in cell A33 of each of the works - I've always had problems getting my
head around this "INDIRECT" function.

For instance in D31 I'm trying to get the result ="SheetnameD1"!A33
using something like:
=INDIRECT("'"&D1&"'!A33",), but there are two problems with this, 1
this returns a #REF! error, and 2 copying it across the rows will not
work.

Help would be appreciated.

TIA

Ron

 

[Pete_UK]

Well, you have a comma just inside the closing bracket which is not
needed:

=INDIRECT("'"&D1&"'!A33")

D1 should just contain a sheet name, exactly as it appears on the tab
- ensure you do not have any spurious spaces at the end.

A33 will not change to B33 etc as you copy across because it is inside
the quotes and thus treated as a literal string. If you do not need to
go beyond Z33, you can do this:

=INDIRECT("'"&D1&"'!"&CHAR(64+COLUMN(A1))&"33")

or you can use the ADDRESS function (particularly if you want to copy
down and thus have the 33 change as well).

Hope this helps.

Pete

 

[RonM]

Pete,

Thanks a million, that worked a treat.

Rin

 

[Pete_UK]

You're welcome, Ron - thanks for feeding back.

Pete