How to reference a worksheet name
 

Im writing on behalf of a colleague who is more familiar with Excel, but Im
more familiar with these discussion groups! Ill see if I can word the
question understandably.

We have an Excel workbook with several sheets, each named with a persons
last name (e.g., €œSmith,€� €œJones,€� €œMartin,€� etc.). All sheets are identical
in structure and have numerous calculated cells on them.

He wants one sheet to be a summary sheet, extracting selected data from each
of the named sheets. Column 1 of the summary sheet will have the name of the
sheet (e.g., €œSmith,€� €œJones,€� €œMartin,€� etc.) Across each row will be the
selected data from the sheet named by the name in column 1.

We know how to write the formulas across the row, and that part works. What
were trying to do is to save the work of having to manually input the
formulas for each cell in each row because it pulls from a different sheet.

If on the summary sheet cell A1 has the name of one of the sheets, e.g.,
€œSmith,€� is there a way to write a formula in A2 that says, €œGo to the sheet
with the name of whats in A1 and copy the contents of C10 from that sheet
into this cell.€�

I guess its like entering a text variable into a formula.

Then, we think, wed be able to use the formula copy down feature and save a
lot of keystrokes and formula entries.

I think I have sufficiently mangled the description and question so Ill
quit and wait for a response!

Thanks in advance.

Jerry

How to reference a worksheet name
 

Assuming B1:M1 (or beyond) is headers, and A2:A20 and beyond is sheet names:
B2: =INDIRECT($A2&"C10")

If you copy this formula down to B3:B20, it will achieve what I believe is
your desired result. With INDIRECT, since the $A2 is not in quotes, the row
number will change as you copy the formula (not the column, as it is
anchored), but the C10 will not change it IS in quotes.

Hope this helps.
--
John C


"JWCrosby" wrote:

Im writing on behalf of a colleague who is more familiar with Excel, but Im
more familiar with these discussion groups! Ill see if I can word the
question understandably.

We have an Excel workbook with several sheets, each named with a persons
last name (e.g., €œSmith,€� €œJones,€� €œMartin,€� etc.). All sheets are identical
in structure and have numerous calculated cells on them.

He wants one sheet to be a summary sheet, extracting selected data from each
of the named sheets. Column 1 of the summary sheet will have the name of the
sheet (e.g., €œSmith,€� €œJones,€� €œMartin,€� etc.) Across each row will be the
selected data from the sheet named by the name in column 1.

We know how to write the formulas across the row, and that part works. What
were trying to do is to save the work of having to manually input the
formulas for each cell in each row because it pulls from a different sheet.

If on the summary sheet cell A1 has the name of one of the sheets, e.g.,
€œSmith,€� is there a way to write a formula in A2 that says, €œGo to the sheet
with the name of whats in A1 and copy the contents of C10 from that sheet
into this cell.€�

I guess its like entering a text variable into a formula.

Then, we think, wed be able to use the formula copy down feature and save a
lot of keystrokes and formula entries.

I think I have sufficiently mangled the description and question so Ill
quit and wait for a response!

Thanks in advance.

Jerry

How to reference a worksheet name
 

First, I think you meant:
=INDIRECT($A2&"!C10")
(added that ! point.)

But I think I'd include the apostrophes to surround the worksheet's name:
=INDIRECT("'" & $A2 & "'!C10")

It won't hurt if they're not necessary.



John C wrote:

Assuming B1:M1 (or beyond) is headers, and A2:A20 and beyond is sheet names:
B2: =INDIRECT($A2&"C10")

If you copy this formula down to B3:B20, it will achieve what I believe is
your desired result. With INDIRECT, since the $A2 is not in quotes, the row
number will change as you copy the formula (not the column, as it is
anchored), but the C10 will not change it IS in quotes.

Hope this helps.
--
John C

"JWCrosby" wrote:

Im writing on behalf of a colleague who is more familiar with Excel, but Im
more familiar with these discussion groups! Ill see if I can word the
question understandably.

We have an Excel workbook with several sheets, each named with a persons
last name (e.g., €œSmith,€� €œJones,€� €œMartin,€� etc.). All sheets are identical
in structure and have numerous calculated cells on them.

He wants one sheet to be a summary sheet, extracting selected data from each
of the named sheets. Column 1 of the summary sheet will have the name of the
sheet (e.g., €œSmith,€� €œJones,€� €œMartin,€� etc.) Across each row will be the
selected data from the sheet named by the name in column 1.

We know how to write the formulas across the row, and that part works. What
were trying to do is to save the work of having to manually input the
formulas for each cell in each row because it pulls from a different sheet.

If on the summary sheet cell A1 has the name of one of the sheets, e.g.,
€œSmith,€� is there a way to write a formula in A2 that says, €œGo to the sheet
with the name of whats in A1 and copy the contents of C10 from that sheet
into this cell.€�

I guess its like entering a text variable into a formula.

Then, we think, wed be able to use the formula copy down feature and save a
lot of keystrokes and formula entries.

I think I have sufficiently mangled the description and question so Ill
quit and wait for a response!

Thanks in advance.

Jerry


--

Dave Peterson

How to reference a worksheet name
 

John,
It took some work and some tweaking, but I got your idea to work. I was
unfamiliar with the INDIRECT function. However, I needed to insert a "!"
before the "C" in your example to get it to work I think we're good to go!

Thanks.

"John C" wrote:

Assuming B1:M1 (or beyond) is headers, and A2:A20 and beyond is sheet names:
B2: =INDIRECT($A2&"C10")

If you copy this formula down to B3:B20, it will achieve what I believe is
your desired result. With INDIRECT, since the $A2 is not in quotes, the row
number will change as you copy the formula (not the column, as it is
anchored), but the C10 will not change it IS in quotes.

Hope this helps.
--
John C


"JWCrosby" wrote:

Im writing on behalf of a colleague who is more familiar with Excel, but Im
more familiar with these discussion groups! Ill see if I can word the
question understandably.

We have an Excel workbook with several sheets, each named with a persons
last name (e.g., €œSmith,€� €œJones,€� €œMartin,€� etc.). All sheets are identical
in structure and have numerous calculated cells on them.

He wants one sheet to be a summary sheet, extracting selected data from each
of the named sheets. Column 1 of the summary sheet will have the name of the
sheet (e.g., €œSmith,€� €œJones,€� €œMartin,€� etc.) Across each row will be the
selected data from the sheet named by the name in column 1.

We know how to write the formulas across the row, and that part works. What
were trying to do is to save the work of having to manually input the
formulas for each cell in each row because it pulls from a different sheet.

If on the summary sheet cell A1 has the name of one of the sheets, e.g.,
€œSmith,€� is there a way to write a formula in A2 that says, €œGo to the sheet
with the name of whats in A1 and copy the contents of C10 from that sheet
into this cell.€�

I guess its like entering a text variable into a formula.

Then, we think, wed be able to use the formula copy down feature and save a
lot of keystrokes and formula entries.

I think I have sufficiently mangled the description and question so Ill
quit and wait for a response!

Thanks in advance.

Jerry

How to reference a worksheet name
 

Thanks for the feedback, yeah, I realized it after I posted (got interrupted
mid-post), and entered the 'correct info', as did Dave Peterson.

Glad to help :)
--
John C


"JWCrosby" wrote:

John,
It took some work and some tweaking, but I got your idea to work. I was
unfamiliar with the INDIRECT function. However, I needed to insert a "!"
before the "C" in your example to get it to work I think we're good to go!

Thanks.

"John C" wrote:

Assuming B1:M1 (or beyond) is headers, and A2:A20 and beyond is sheet names:
B2: =INDIRECT($A2&"C10")

If you copy this formula down to B3:B20, it will achieve what I believe is
your desired result. With INDIRECT, since the $A2 is not in quotes, the row
number will change as you copy the formula (not the column, as it is
anchored), but the C10 will not change it IS in quotes.

Hope this helps.
--
John C


"JWCrosby" wrote:

Im writing on behalf of a colleague who is more familiar with Excel, but Im
more familiar with these discussion groups! Ill see if I can word the
question understandably.

We have an Excel workbook with several sheets, each named with a persons
last name (e.g., €œSmith,€� €œJones,€� €œMartin,€� etc.). All sheets are identical
in structure and have numerous calculated cells on them.

He wants one sheet to be a summary sheet, extracting selected data from each
of the named sheets. Column 1 of the summary sheet will have the name of the
sheet (e.g., €œSmith,€� €œJones,€� €œMartin,€� etc.) Across each row will be the
selected data from the sheet named by the name in column 1.

We know how to write the formulas across the row, and that part works. What
were trying to do is to save the work of having to manually input the
formulas for each cell in each row because it pulls from a different sheet.

If on the summary sheet cell A1 has the name of one of the sheets, e.g.,
€œSmith,€� is there a way to write a formula in A2 that says, €œGo to the sheet
with the name of whats in A1 and copy the contents of C10 from that sheet
into this cell.€�

I guess its like entering a text variable into a formula.

Then, we think, wed be able to use the formula copy down feature and save a
lot of keystrokes and formula entries.

I think I have sufficiently mangled the description and question so Ill
quit and wait for a response!

Thanks in advance.

Jerry

response to question about sheet referencing
 

There are two reasonable solutions I've used:

1) First, set up one column with all references as desired, making sure that all row/column references are fixed (e.g. 'Smith'!$C$3, not 'Smith'!C3). Then copy that column into the remaining columns. Finally, for each column after the first one, select the appropriate cells within that column, then use Ctrl-H (Find and Replace) to change 'Smith' to 'Jones', 'Smith' to 'Martin', etc. This will replace the text within the formula so that it refers to the appropriate sheet.

2) A second approach is to use the INDIRECT worksheet function. The only argument in this function is a text string, which you can construct with standard cell references. For example, suppose that in Column B of your summary sheet, you want to display the value in cell B2 on each of the named worksheets ('Smith', 'Jones', 'Martin', etc.). Then set it up as follows:

Col A Col B
Row 1 Smith =INDIRECT(A1 & "!B2")
Row 2 Jones =INDIRECT(A2 & "!B2")
Row 3 Martin =INDIRECT(A3 & "!B2")

Take note of a few considerations:
(i) There is no need for any $'s in the B2 reference, since it is contained in quotes as part of the text string being constructed (although including them wouldn't cause any problems)

(ii) The obvious advantage of this is that you can copy / fill the formula from the first row into the remaining rows

(iii) It may be necessary to include single quotes around the sheet name if there are any spaces in the sheet name [e.g. =INDIRECT("'" & A1 & "'!B2")]

(iv) If there are many columns to fill, you can use an additional cell reference within the text string to avoid having to edit each column's formula:

Col A Col B Col C ...
R1 "!B2" "!C2"
R2 Smith =INDIRECT($A1&B$1) =INDIRECT($A1&C$1)
R3 Jones =INDIRECT($A2&B$1) =INDIRECT($A2&C$1)
R4 Martin =INDIRECT($A3&B$1) =INDIRECT($A3&C$1)
..
..
..

In case it's not clear already, the first method is probably best if there are relatively few tab names to replace. The second one is a bit trickier to set up but much quicker if there are, say, 20 or more tabs.

I hope this helps!