Try it like this:

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

--
Biff
Microsoft Excel MVP


"jack" wrote in message
...
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