Lookup different sheets and get the value from the sheet specified

P

Prasad Madineni

Hi,
I am not sure whether it is possible or not. I will try to explain my
situation.
There are 10 guys in the team and we have 10 sheets created in a work book
and all are of same standard format. I have named the sheets with name of the
person.

Now What I need is: I will have a main sheet lets say 'Summary' and one cell
will have drop down list with name of persons in the team. When I select the
name of person, automatically other cells should get filled up with the
details from the spreadsheet related to that person. Hope I am able to
explain clearly.

Ex: Summary sheet and Tim, Rob, John, Nancy are the sheets in the workbook
and all are of same standard format. In summary sheet in a cell if I enter
the TIM, remaining cells should take up the values from TIM spreadsheet. How
can I do that? Will lookup function for this?
Please help me out..
 
E

Elkar

You can use the INDIRECT function for this. Let's say the name you've
selected from your drop-down list is stored in cell A1, then use this formula:

=INDIRECT(A1&"!B2")

This will return the value of the cell B2 on the sheet with the name that
matches the value stored in A1 of this sheet. You can also use this within
other functions. For example:

=SUM(INDIRECT(A1&"!B1:B10))

This will give you the sum of the range B1 thru B10 on the sheet referenced
in cell A1.

HTH,
Elkar
 
Top