Conditional Formatting Between Sheets With Mismatched Rows

[zero269]

Hello,

I'm having some issues with Conditional Formatting a column in one sheet based on a column in another sheet. The Conditional Formatting only works if both Sheets have all of the same lookup values. If the value doesn't exist in Sheet 2 for example, the value in Sheet 1 will return TRUE instead of FALSE, even though I'm using a "Greater Than 0" condition.

For example, I deleted a value in Sheet 2 (Student), Row 13, and on Sheet 1 (Books), that referenced value (153447 in Row 13) changed from FALSE to TRUE.

What I'm trying to achieve is to highlight cells in Column B (Sheet 1) when a cell in Column B (Sheet 2) has a value greater than 0.

I've tried using the following formula to achieve my results when testing in a cell:

[FONT=Courier New]=IF(XLOOKUP($A2,Student!$A$2:$A$13,Student!$B$2:$B$13,"",0)>0,TRUE,FALSE)[/FONT]

This one changes the results from FALSE to TRUE when I clear a value in Sheet 2.

[FONT=Courier New]=IF(XLOOKUP($A2,Student!$A$2:$A$13,Student!$B$2:$B$13)>0,TRUE,XLOOKUP($A2,Student!$A$2:$A$13,Student!$B$2:$B$13))[/FONT]

This one changes the results from 0 to #N/A when I clear a value in Sheet 2.

I'm assigning a Quiz # to a Student, and that assignment is tracked using the letter x in the Column B of Sheet 1 (Books).

Once the Student takes the Quiz, their Score is recorded in Sheet 2. I would like to see in Sheet 1 that the Test was completed using the Conditional Formatting to automate the tracking.

This 12 Row Workbook is a condensed version where the original file has over a few thousand Books/Tests and several Students. The Books sheet (1) will never have the same number of records as any of the Students sheets (2+). Right now, they are around 200-300 only, which means there are a few thousand false TRUE's being conditionally highlighted.

Hopefully I'm making sense here…

Here are two screenshots that show both sheets in their entirety. As seen in Sheet 1, Column B is conditionally formatted based on a TRUE value as tested using Column D.. I used Column C to confirm the correct Score values from Sheet 2 before building out the formula tested in Column D as seen above. This was the formula I sued for the Conditional Formatting.

 

[Fluff]

How about

=XLOOKUP($A2,Student!$A$2:$A$13,Student!$B$2:$B$13,0,0)>0

 

[Fluff]

Another option if the values in sheet2 col A will never repeat

=SUMIFS(Student!$B$2:$B$13,Student!$A$2:$AB$13,$A2)>0

 

[zero269]

How about

=XLOOKUP($A2,Student!$A$2:$A$13,Student!$B$2:$B$13,0,0)>0

Hello Fluff,

You're first suggestion works like a charm! I didn't try your 2nd suggestion because your first one worked perfectly, and I needed only to change my "if_not_found" value from (blank) "" to 0 which made it much easier.

[FONT=Segoe UI Light]=XLOOKUP($A2,Student!$A$2:$A$13,Student!$B$2:$B$13,0,0)>0[/FONT]

I can't believe I didn't try returning a 0 instead of a blank value (""). Obviously I still have a lot to learn. I've been using Excel since Window 95, but Formulas have always been my nemesis.

Just to recap the issue and solution for other site visitors:

ISSUE:

Need Column B cells in Sheet 1 to highlight when Column B in Sheet 2 have a value greater than 0.
Both sheets have unique values in Column A that need to be matched prior to applying the conditional formatting.

Example:
When a Score is entered in Sheet 2 for Quiz #5208, the corresponding Quiz number in Sheet 1 needs to be highlighted in Column B.

This allows me to see which books have been tested for a given Student. Each student has a column in Sheet 1, and that x indicates that Quiz was assigned to that Student. The highlighting just makes it easier to see which ones have already been tested on without having to check the Students individual Sheet and searching for the Quiz number.

A huge timesaver to say the least...

One thing to note was that when I was returning a blank value ("") instead of 0, cells that were blank were showing TRUE and cells for Quizzes that didn't exist on the Student sheet were also showing TRUE.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[zero269]

How about

=XLOOKUP($A2,Student!$A$2:$A$13,Student!$B$2:$B$13,0,0)>0

Hi Fluff,

Would it be possible to expand on this formula to validate across multiple sheets in order to highlight the corresponding columns in Sheet 1 (Books)?
The reason I ask is that I would like this same behavior for each Student column in Sheet 1 (Books). I have only a handful of Student columns right now, so having a CF for each Student is not an issue. However, combining them into one would be optimal.

For example, let's say I have the following Student Sheets: Student 1 up to Student 12. Each sheet is a direct copy from the first, so the structure is the exact same. However, one thing to note is that not all Students have the same number of assigned Quizzes. Some may have only a dozen Rows while others a couple hundred; depending on Grade (K-8).

Now in Sheet 1 (Books), each of these Students have a dedicated Column to track Quiz number assignments.

Referencing the multiple columns in Sheet 1 seems obvious to me: Books!$B$2:$C$13
However, combining the appropriate Sheets in a range is the challenge.

I was experimenting with:

=XLOOKUP($A2,Student1:Student2!A2:A13,Student1:Student2!B2:B13,0,0)>0

However, I can see the flaw in that it's trying to return multiple values into a single cell for the return_array.

I found Microsoft's article on 3-D References, but I'm not sure that will truly line up with my Conditional Formatting goal considering they show combining values across multiple sheets into a single cell.

My goal is to expand the Conditional Formatting across multiple columns in Sheet 1 (Books) to achieve the following: ignoring the Test columns of course...

 

[Fluff]

If the quiz number appears on multiple sheets, then you will need to create a rules for each student.