Indirect function problem

[jack]

I have an inherited a workbook with numerous sheets (tabs) named in the
format of last name,first initial (for example: Jones,R -- there's no space
between the last name and first initial, just the comma). I have a brief
macro that lists these worksheet names in column A.
I am attempting to collect several columns summary data from all the
worksheets by using the formula =INDIRECT(A1&"!$A$7") (as an example) and
copying it down column B. The result is #REF! . It appears that the comma
in the worksheet names is causing the #REF! error message. I don't
understand why this is occurring. Is there a way I can correct for this in
the formula without going thru the long process of renaming each of the
numerous worksheet tabs?
Or if there is a way to accomplish this through VBA, let me know if I should
repost in the Excel Programming newsgroup. I'm not too skilled in VBA and
would prefer to modify the formula, if possible.
Any help will be greatly appreciated.
Jack

 

[T. Valko]

Try it like this:

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

 

[Sheeloo]

Use
=INDIRECT("'" & A1 &"'!$A$7")

The string within INDIRECT() should evaluate to
'Jones,R'!$A$7

You need to put the sheetname within '' like this.

Let us know how it goes.

'Sheet Name' if it contains a space or comma etc...

 

[jack]

Thanks!!!!!!!
That worked !!! I was hoping for a simple solution and that was it!
Jack

Try it like this:

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

 

[T. Valko]

You're welcome. Thanks for the feedack!