indirect formula syntax

[Graeme]

Hello,
I have been given a workbook with two worksheets. The first sheet is named
summary and the second sheet is named ...o&b. I am trying to use the
indirect formula to return a value into the summary sheet from the ...o&b
sheet. So far I have tried the following in the summary sheet.

A1 B1
'...o&&b' =indirect(A1,"!C16")
i.e I am attempting to lookup the value in ...o&b!c16 and return it to cell
B1 by reference to cell A1.
However, this doesn't seem to work. I think the ampersand is an illegal
character. I can't change the worksheet names as the workbook has been
exported from a centralised mainframe.

Any help appreciated.

Graeme.

 

[Pete_UK]

You need to build up your composite string representing the cell reference
and then pass it as a parameter to INDIRECT, like this:

=INDIRECT(A1&"!C16")

Hope this helps.

Pete

 

[Dave Peterson]

Sometimes, worksheet names require apostrophes around their names.

If they have spaces, if they're numeric, if they look like addresses...

=INDIRECT("'"&A1&"'!c16")

Those apostrophes won't hurt if you don't need them, either.