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

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

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" wrote:

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

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

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

You're welcome. Thanks for the feedack!

--
Biff
Microsoft Excel MVP


"jack" wrote in message
...
Thanks!!!!!!!
That worked !!! I was hoping for a simple solution and that was it!
Jack

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

You have to put in some apostrophes around the sheet name, too.

=INDIRECT("'"&H3&"'!A2")

jack;247557 Wrote:
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


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=69056