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:
This one changes the results from FALSE to TRUE when I clear a value in Sheet 2.
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.
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.