How to make a custom conditional formatting formula point to the contents of a cell reference

2
votes

I have a spreadsheet with a custom formula that changes the colour of different cells based on the value in the dates column. I also frequently have to change the formula based on different times of the month.

I would like the formula to point to a cell eg A1 so that whenever I change the contents of A1, the custom formula itself will change.

Currently the custom conditional formatting formula is =E2>=TODAY() -21

I alternate between this and =AND(E2>DATE(2019,3,23),E2<DATE(2019,4,20))

I've tried to use =INDIRECT(A1) as the formula, with A1 cell content =E2>=TODAY() -21 but this doesn't work. I would really appreciate any tips on how to get this working.

1
google-sheetsgoogle-sheets-formulags-conditional-formatting
can you share your sheet? – player0

1 Answers

0
votes

custom formula:

=IF((($A$1="=E2>=TODAY()-21")*(E2>=TODAY()-21))+
    (($A$1="=AND(E2>DATE(2019,3,23),E2<DATE(2019,4,20))")*((E2>DATE(2019,3,23))*(E2<DATE(2019,4,20)))),1)

0

demo spreadsheet