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