Sorry about that, I've been flying between jobs here and didn't focus as well as I should have on this thread I started.
I have a "Summary" sheet that pulls information into 17 columns from 17 location sheets. The location sheets (3-letter sheets - i.e. BGE, PAH, PDD, etc) have the employees first and last names, along with the days of the month (1-31) where the employees worked hours are entered (see example).
In the summary sheet, which Fluff helped with to get the formula needed for each cell in the 17 columns, the location sheets are listed by column (see example.)
If an employee worked at more than 1 location (i.e. there are hours showing in more than 1 column on the Summary sheet for that employee, then the row needs to be highlighted.
I'll make the examples short and sweet.
Location sheet (called
BGE) example:
LAST NAME | FIRST NAME | WAL | 1 | 2 | 3 | 4 | 5 |
---|
Doe | Jane | x | 8 | 8 | 8 | | |
West | Tom | x | | | | 8 | 8 |
Yelle | Bill | | | | | | |
In this example, Jane worked at location BGE on the 1st-3rd of the month at 8 hours each day, while Tom worked at that location on the 4th & 5th for 8 hours each day. Again, there are 17 of these "location" sheets in the workbook. WAL = Worked At Location. If there's no X, then the person didn't work there (this is for sorting.)
Location sheet (called
PAH) example:
LAST NAME | FIRST NAME | WAL | 1 | 2 | 3 | 4 | 5 |
---|
Doe | Jane | | | | | | |
West | Tom | | | | | | |
Yelle | Bill | x | 8 | | 8 | | 8 |
In this example, only Bill works at location PAH and worked 8 hours on the 1st, 3rd & 5th of the month.
Summary sheet example:
LAST NAME | FIRST NAME | FIXED | BGE | PAH | PDD | ETC | ETC |
---|
Doe | Jane | | 24 | | | | |
West | Tom | | 16 | | | | |
Yelle | Bill | | | 24 | | | |
In this example it shows the hours each employee worked at each location (only BGE & PAH were used so the others are empty.) Fixed just equals fixed pay and has an X if that's the case.
The cells below each location sheet column have the code from Fluff (seen in bold blue below) to pull the hours worked data in from all of the location sheets.
There are 76 rows and the columns go up to AH.
=IFERROR(SUM(INDEX(BGE!$D$2:$AH$76,MATCH(A2&"|"&B2,INDEX(BGE!$A$2:$A$76&"|"&BGE!$B$2:$B$76,0),0),0)),0)
I need help highlighting each row with hours showing in
more than one location column, please.
Again, I was zipping between jobs and did not give this thread the proper info needed for anyone to accurately assist me and I apologize again for that.
Hopefully this clears it up and you're able to assist me further.
Thanks Joe!