Copy a cell from one sheet to another based on the sheet name equaling a cell

kingdingathing used Ask the Experts™
Hi eveyone,

Probably an easy one for you.

I have a spreadsheet with 113 worksheets. One of those worksheets (being sheet 1) is a sheet that is to collate data from one cell in every other worksheet. Every sheet (except sheet 1) is named very differently, specificly people names.

What I'm after is... in sheet 1, I would like to copy the contents of one cell in one of the other 112 sheets to sheet 1. BUT the formula must be the same in evey cell in sheet 1!

In sheet 1 I have every persons name listed, from A5:A115. So I want to compare the name in column A with the sheet name and if it = TRUE then copy the contents of a specific cell.

So Im hoping one of you gurus can help me with some formula magic!!

Hope that makes sense...


Last Name | First Name | Total
Smith     | John       | $250.00 \
Anderson  | Jane       | $75.00  /  Formulas must be the same

--Smith, John-- '(aka Sheet2)
Total     |
$250.00   |

--Anderson, Jane-- '(aka sheet3)
Total     |
$75.00    |

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Assuming that you have

 - 'Last Name' in column A on sheet1
 - 'First Name' in column B on sheet1
 - 'Total' in column C on sheet1
 - the total value (250 & 75) in cell D9 on each person sheet

you can use the formula

=INDIRECT( "'" & A2 & ", " & B2 & "'!D9")

for cell C2 on sheet1, and drag it down to populate all appropriate cells in column C of sheet 1.

Of course you can change the D9 cell to any arbitrary cell.


What can I say but AWESOME, works perfectly!

Thanks a million!


AWESOME! So clean and simple! :)
you're welcome !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial