| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summary worksheet reference to detail worksheet | New Users to Excel | |||
| I want in one worksheet to relatively link to/reference cells in another without changing the format of the current worksheet. | Excel Discussion (Misc queries) | |||
| Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
| Can the offset worksheet function reference another worksheet | Excel Worksheet Functions | |||
| Worksheet reference (i.e placing worksheet name in a cell) | Excel Worksheet Functions | |||
Linear Mode
