Conditional Formatting Between Sheets With Mismatched Rows

zero269

Active Member
Joined
Jan 16, 2023
Messages
253
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Formula:
[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.

Excel Formula:
[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.

1673931728799.png
1673931745014.png

 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
How about
Excel Formula:
=XLOOKUP($A2,Student!$A$2:$A$13,Student!$B$2:$B$13,0,0)>0
 
Upvote 0
Solution
Another option if the values in sheet2 col A will never repeat
Excel Formula:
=SUMIFS(Student!$B$2:$B$13,Student!$A$2:$AB$13,$A2)>0
 
Upvote 0
How about
Excel Formula:
=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.

Excel Formula:
[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.

1673984921719.png
1673984930989.png
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
How about
Excel Formula:
=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:
Excel Formula:
=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...

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

Forum statistics

Threads
1,223,227
Messages
6,170,847
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top