J
JWCrosby
I’m writing on behalf of a colleague who is more familiar with Excel, but I’m
more familiar with these discussion groups! I’ll see if I can word the
question understandably.
We have an Excel workbook with several sheets, each named with a person’s
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
we’re 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 what’s in A1 and copy the contents of C10 from that sheet
into this cell.â€
I guess it’s like entering a text variable into a formula.
Then, we think, we’d 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 I’ll
quit and wait for a response!
Thanks in advance.
Jerry
more familiar with these discussion groups! I’ll see if I can word the
question understandably.
We have an Excel workbook with several sheets, each named with a person’s
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
we’re 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 what’s in A1 and copy the contents of C10 from that sheet
into this cell.â€
I guess it’s like entering a text variable into a formula.
Then, we think, we’d 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 I’ll
quit and wait for a response!
Thanks in advance.
Jerry
