I’m using Google Sheets. In column A there are dates at irregular intervals. In column B there are names of fruit. I’d like to add some conditional formatting depending on the date corresponding to the fruit or, if the fruit doesn’t have one, the lowest one above it (you may assume there will always be a date in cell A1). The effect is that a date affects the fruit next to it and all consecutive fruits up until the next date given.
I imagine I’d have to add a lookup by criterion NOT(ISBLANK()) to the conditional formatting custom formula. Is there a function that could do that? Is there some other way I can achieve the desired result?
Example image:
if you can use column C as a helper column to have a date in every row, the conditional formatting would be pretty simple:
you’d just have to set C1=A1, then you can use the formula at the top for the other rows and base the conditional formatting on column C.
edit to add: I wasn’t sure how you were picking which date to highlight, so I just set it up to enter a date in D1 and highlight any with that date.