highlighting cells with no value if they are between cells with value

[apor]

Hello everyone,

I am currently analzying scanning data in a warehouse. The purpose is to find out which days a certain product was not scanned and to highlight it.

The data i am working with is a list of every product scanned and the day on which it was scanned. Then, I used a Pivot Table with y axis product and x axis day. If product A was scanned once on Monday, the field (Product, Day) has a 1. If it was scanned twice, a 2 and so on. I want to highlight the days it was not scanned at all.

I added an image of how i would like the cells highlighted. The added complication in this case is that i cannot simply highlight blank cells, since for example, Product C was not introduced until Wednesday and it is therefore not a mistake that it was not scanned before then. Same goes with for example product F after Friday, since that was the last day it was there.

Any help with this would be greatly appreciated.

Thanks in advance and greeting,

Anna

 

[apor]

I am afraid that you selected the wrong area when you setup the conditional format.
That formula is valid if you select from B2 to the end of your table; if the selected area at the moment is different you will get wrong results
So, my suggestion:
-remove the current conditional format rules
-select B2:XXyy
-apply the conditional format using the given formula

Thank you so much for your time! This worked perfectly

 

[apor]

I am afraid that you selected the wrong area when you setup the conditional format.
That formula is valid if you select from B2 to the end of your table; if the selected area at the moment is different you will get wrong results
So, my suggestion:
-remove the current conditional format rules
-select B2:XXyy
-apply the conditional format using the given formula

If you have the time, could you give me a breakdown of what the string means? AND i know returns values if a condition is met, and then three conditions are stated

B2: "" means the cell has to be empty
however, what exactly do the SUM strings mean and how do they work together? and how does the string work throughout all cells of the table, not just B2 which is stated? is that due to CC2, which defines the last selected column? or because strings in conditional formatting "loop"?

 

[Anthony47]

=AND(B2="",SUM($B2:B2)>0,SUM(B2:$CC2)>0)

The key is the type of absolute/relative addressing, and let's examine how it works on cell D3
When all
-"D3" is empty
-D2:D3 contains some values (the product has already been scanned in the past...)
-D3:CC3 contains some value (... and has some scanning in the future)
then the color will be applied to the cell

In other words, $B2 means "from column B of the current row; B2 when copied in eanche of the selected cells become "the current cell"; $CC means "up to column CC of the current row"
That's why it is important which area is selected when you set the conditional format

If that resolve the problem then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution