Conditional Formula that references another column's text while keeping between two dates.

Question

Hello!

Right now I'm working on a conditional formula that will hopefully work with a filter system.

I am currently using the Conditional format Between =TODAY() - =TODAY()+30 to highlight cells that are coming up in 30 days. and I just changed the color based on what cell I wanted to be different.

However because (I assume) they are all the same formula (or because they're cell specific?) when I apply a filter to sort by date/name/etc. the conditional formula doesn't move with the filtering. It still highlights with a color when a date is coming up in 30 days from today. but not the correct color.

What I want What happens when I sort
----------

But I also want it to reference a different cell with specific text, In the above example I want everything in 5's Highlight Blue.

So I've been trying to find or come up with a formula that references Column A while also keeping in mind dates between today and 30 days from today. But I haven't had any luck.

Example: If dates are between Today and 30 days from today, and the text in column A is 5 Highlight Blue

Could someone help me with this, or offer a different solution?

Thank you all so much in advance.

Answer 1

Hi,

Here is one possible solution:

=AND(B2>=TODAY(),B2<=TODAY()+30,A2=5)

Use this as your conditional formatting formula.

Shane

Answer 2

Glad we could help,

Shane

Answer 3

Hello Shane!

So, your formula didn't work quite right, but it was on the right track! I only needed to make a minor adjustment to get it to do what I wanted.

=IF(AND(B1>=TODAY(),B1<=TODAY()+30),A1="5")

So thank you SO MUCH!