indirect cell reference using copies of worksheets in same workboo

[JT Spitz]

Whenever I copy a worksheet in the same workbook, I get the name followed by
(#). EG worksheet February copies to a new worksheet named February(2).
When i reference that worksheet name in the indirect function through the
cell I set up for the worksheet name, I get the REF error. Does indirect not
work with parenthesis?

The help screen doesn't really discuss this and all the information I
previously received only mentioned spaces in my worksheet names.

I have already figured out that if I change the name to something like
FebruaryB, it would work, but I would like to find out if this is absolutely
necessary.

Thanks.

 

[Gord Dibben]

JT

Note that February (2) contains a space.

Sheetnames with spaces must be surrounded by quotes. That's why your change
to FebruaryB works.....no space.

Try =INDIRECT("'"&K3&"'!F5")

Where K3 contains the sheetname February (2)


Gord Dibben Excel MVP

 

[JT Spitz]

While I agree that a space is put in automatically, I manually edited that
out so that is read February(2). And I still got the REF error. So the
question is still, does Indirect work with parenthesis?

Thanks

JT

 

[Gord Dibben]

JT

On further experimentation, I find I still must enter the single quotes around
the sheetname no matter if a space or not when the name contains parens.

So.....Indirect does not seem to work with parens unless the single quotes are
added.

At least, in my experience.


Gord

 

[JT Spitz]

Okay, thanks for the assist. It will be easier for me to just change the
worksheet tab names. Removing the parens causes the names to become dates,
which also causes the REF error and there are too many other cells using the
Indirect function for me to edit them all.

How does the saying go, you pay your dime and take your chances? Guess this
is going to cost me a quarter, at least.

Have a good one.

JT