Sheet names not being recognised using "INDIRECT"

**spew** · 02-22-2012, 09:17 AM

Hi all,

Just going crazy trying to sort out a problem. I am using the "INDIRECT" function (example =INDIRECT($B132&"!$ap$4") ) to pick up information from a range of 160 sheets in an excel file. All the sheets are named with a four digit alpha numeric code. For some reason sheets names R1R2 R1R4 R2R1,etc are not recognised and I get an invalid cell reference error returned. If I change the sheet names and the INDIRECT function details to match then all works ok but not with any of the above type names.

Any ideas?

**Richard Buttrey** · 02-22-2012, 09:21 AM

Hi,

Upload your workbook so that we can see your problem in context

**NBVC** · 02-22-2012, 09:23 AM

Try enclosing in apostrophes...

e.g.

=INDIRECT("'"&$B132&"'!$ap$4")

**spew** · 02-22-2012, 09:30 AM

Ok the original is too large but here is a cut down version

**NBVC** · 02-22-2012, 09:32 AM

Did you try my suggestion?

**spew** · 02-22-2012, 09:35 AM

Just tried NBVC,s suggestion and it and it works. Thansk so much. Dont know why 155 other letter/number combinations worked ok but not the 5 R1R2 type combos.
Thanks again.

**NBVC** · 02-22-2012, 09:38 AM

It think it might be confusing Excel because Excel uses R1C1 referencing as well as A1 referencing. The apostrophe's "force" Excel to recognize the sheetname as a sheetname.