Stumped - multiple lookup criteria across worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try this:

Enter this formula in Roster B13 and copy down as needed:

=VLOOKUP(A$1,INDIRECT("'"&A13&"'!B1:C10"),2,0)
 
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
 
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
Click to expand...

I'm not following you on the #REF! errors.
 
Back
Top