Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stumped - multiple lookup criteria across worksheets
I have 100 plus sheets in a workbook.
Each has the same layout for data entry. Each sheet tab is the name of an employee. In (B1:B10) I enter a date. In (C1:C10) is data I need to display on a master sheet called "Roster". The Roster sheet in A13:A20 will contain the names of employees as an example. A1 in the Roster sheet will have the current date. How can I match the name in A13 of the Roster with the Date in A1 of the Roster and return the corrosponding data by date from the worksheet of the employee in A13. This value is displayed on the Roster sheet in B13. I am stumped. Thank you, Martin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stumped - multiple lookup criteria across worksheets
Try this:
Enter this formula in Roster B13 and copy down as needed: =VLOOKUP(A$1,INDIRECT("'"&A13&"'!B1:C10"),2,0) -- Biff Microsoft Excel MVP "Trying to Excel" wrote in message ... I have 100 plus sheets in a workbook. Each has the same layout for data entry. Each sheet tab is the name of an employee. In (B1:B10) I enter a date. In (C1:C10) is data I need to display on a master sheet called "Roster". The Roster sheet in A13:A20 will contain the names of employees as an example. A1 in the Roster sheet will have the current date. How can I match the name in A13 of the Roster with the Date in A1 of the Roster and return the corrosponding data by date from the worksheet of the employee in A13. This value is displayed on the Roster sheet in B13. I am stumped. Thank you, Martin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stumped - multiple lookup criteria across worksheets
Thank You,
Worked great. The only problem is if the supporting sheets contain no value I get an #N/A message. I also get a #REF Error as well because I am using Row function to bring in the values to the Roster sheet in column A13:A20. Can I use a conditional format of some kind to eliminate it or do you have a better solution? Thanks again, Martin "T. Valko" wrote: Try this: Enter this formula in Roster B13 and copy down as needed: =VLOOKUP(A$1,INDIRECT("'"&A13&"'!B1:C10"),2,0) -- Biff Microsoft Excel MVP "Trying to Excel" wrote in message ... I have 100 plus sheets in a workbook. Each has the same layout for data entry. Each sheet tab is the name of an employee. In (B1:B10) I enter a date. In (C1:C10) is data I need to display on a master sheet called "Roster". The Roster sheet in A13:A20 will contain the names of employees as an example. A1 in the Roster sheet will have the current date. How can I match the name in A13 of the Roster with the Date in A1 of the Roster and return the corrosponding data by date from the worksheet of the employee in A13. This value is displayed on the Roster sheet in B13. I am stumped. Thank you, Martin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stumped - multiple lookup criteria across worksheets
There are several ways to either eliminate (trap) or hide any errors you may
get. This will trap *all* errors and leave the cell blank: =IF(ISERROR(VLOOKUP(A$1,INDIRECT("'"&A13&"'!B1:C10 "),2,0)),"",VLOOKUP(A$1,INDIRECT("'"&A13&"'!B1:C10 "),2,0)) This will trap *only* the #N/A: =IF(ISNA(VLOOKUP(A$1,INDIRECT("'"&A13&"'!B1:C10"), 2,0)),"",VLOOKUP(A$1,INDIRECT("'"&A13&"'!B1:C10"), 2,0)) You could also use conditional formatting to hide the errors. They're still there, you just can't see them. However, it's usually best to trap them rather than just hide them. I am using Row function to bring in the values...get a #REF I'm not following you on the #REF! errors. -- Biff Microsoft Excel MVP "Trying to Excel" wrote in message ... Thank You, Worked great. The only problem is if the supporting sheets contain no value I get an #N/A message. I also get a #REF Error as well because I am using Row function to bring in the values to the Roster sheet in column A13:A20. Can I use a conditional format of some kind to eliminate it or do you have a better solution? Thanks again, Martin "T. Valko" wrote: Try this: Enter this formula in Roster B13 and copy down as needed: =VLOOKUP(A$1,INDIRECT("'"&A13&"'!B1:C10"),2,0) -- Biff Microsoft Excel MVP "Trying to Excel" wrote in message ... I have 100 plus sheets in a workbook. Each has the same layout for data entry. Each sheet tab is the name of an employee. In (B1:B10) I enter a date. In (C1:C10) is data I need to display on a master sheet called "Roster". The Roster sheet in A13:A20 will contain the names of employees as an example. A1 in the Roster sheet will have the current date. How can I match the name in A13 of the Roster with the Date in A1 of the Roster and return the corrosponding data by date from the worksheet of the employee in A13. This value is displayed on the Roster sheet in B13. I am stumped. Thank you, Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup using multiple sheets and multiple criteria, sorry if 2 pos | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) | |||
lookup for criteria from either of 2 worksheets. | Excel Discussion (Misc queries) | |||
Stumped on Lookup Function | Excel Discussion (Misc queries) | |||
Stumped by a lookup scenario | Excel Worksheet Functions |