I am trying to set conditional formatting rules based on dates such that a date entered into one tab in the Google Sheets is compared with a date in another tab, which is in turn selected based on a value in another tab. The application here is to have dates turn red when they fall outside a date range that is chosen based on a cycle number. For example, if cycle 005 runs from 05/08/17 to 05/21/17 and the date entered in a range of cells (there is a "from" and "to" column) is 05/07/17 (before the earliest date in the cycle) or 05/20/18 (after the latest date in the cycle) then the cell will turn red.
I have one tab (called lookup_table) that has cycle numbers and their corresponding "from" and "to" dates, another tab (called review) where the cycle number for a given data set is entered (into cell B2), and a third data tab where "from" and "to" dates are entered. The formula needs to reference "from" and "to" dates in the lookup_table tab based on the cycle number in the review tab to conditionally format the dates entered into the data tab.
I have found some similar questions about conditional formatting but nothing involving putting together a "nested" function comparing dates after referencing them based on another value.
