Forum Discussion
Will a formula work in Conditional Formatting?
I have a spreadsheet with 2 pages.
The first tab (master list) lists a person's username and the classes they have to complete.
The second tab (completion report) lists usernames, the class ID and the person's completion status.
What I would like is, on the first spreadsheet (master list), for the class number to turn green if the person successfully completed it, red if they were unsuccessful and orange if they show not evaluated.
I just can't seem to be able to figure out a formula that would work.
First Tab looks like this
| A | B | D | E | |
| 1 | Employee Username | Course ALEXRCT1128 | Course ALEXRCT1115 | Course ALEXRCT1234 |
| 2 | A1234 | 00535161 | 00491615 | 00496013 |
| 3 | A1235 | 00535161 | 00491615 | 00496013 |
| 4 | A1236 | 00535161 | 00491615 | 00496013 |
Second tab looks like this
| A | B | C | |
| 1 | Username | Class Number | Completion Status |
| 2 | A1234 | 00535161 | Not Evaluated |
| 3 | A1234 | 00491615 | Successful |
| 4 | A1234 | 00496013 | Successful |
| 5 | A1235 | 00535161 | Not Evaluated |
| 6 | A1235 | 00491615 | Not Evaluated |
You can use XLOOKUP to find the row in the completion report that matches the combination of employee username and course ID in the master list, then return the completion status of that row in the completion report (or "No data" if it's not found).
Then test if the returned value is "Successful".
The important thing here is that the lookup_value has a fixed column for column A and a fully relative reference in column B. The lookup_array is the concatenated values for columns A and B of the completion report.
=XLOOKUP( $A2&B2, 'COMPLETION REPORT'!$A$2:$A$6&'COMPLETION REPORT'!$B$2:$B$6,'COMPLETION REPORT'!$C$2:$C$6, "No data" )="Successful"Maybe like in the attached file. In the first step i pulled the completion status to the master sheet with VLOOKUP. Then i referred to the completion status within the master sheet with 3 rules for conditional formatting.
