I need to create a list from this data set that shows item#, qty, and date needed. The main criteria would be the inventory quantity (column B) and I need the list to show what date the cumulative sum of each row exceeds that quantity in column B. The highlighted cells is the data I would like to have listed out with the date they correspond with in Row 1.
You can achieve using two Conditional Format Rules. However, I was only able to accomplish this by inserting a blank column after Inventory. So, column C is a new blank column.
Select your data (D2:J5 in my case) and input these two conditional format rules:
=SUM($D2:D2)>$B2 --> (format as yellow fill)
=AND(SUM($D2:D2)>$B2,SUM(C2:$D2)>$B2) --> (format as no fill)
The first rule will perform a cumulative sum and highlight the first instance plus everything to the right when the rule is triggered.
The second rule basically "unhighlights" everything to the right of the first instance.
You need to arrange the rules so that rule 2 runs first, as seen in the picture.
It's odd that it works this way, specifically arranging in that order, but it worked on my PC.
If you have Office365 you could use:
=IFERROR(INDEX(C$1:H$1,XMATCH(TRUE,SCAN(0,C2:H2,LAMBDA(a,b,a+b))>=B2)),"enough inventory")
