Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup different sheets and get the value from the sheet specified
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.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup different sheets and get the value from the sheet specified
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 "Prasad Madineni" wrote: 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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|