Forum Discussion
DevinDevin
Jun 25, 2023Copper Contributor
Let(c,COLUMN(x), c+f(x)>x) Columns within a let incorrect results in conditional formatting.
I've recreated a simple example of the issue I am having.
The column formula seems go break my conditional formatting, even though the same formula returns true when placed in a cell.
Am I missing something? Or is this unexpected behavior?
More Info:
Here is the actual formula I am attempting to use. It returns TRUE and FALSE correctly if I place it in a cell and change the referenced cell manually, but it appears that using the column() within a let() causes the formula to fail when used in a conditional formatting formula.
=LET(c,COLUMN(D8),t,ROUND(OFFSET(D8,0,-c+2),9),j,INDIRECT(ADDRESS(5,IF(ISODD(c),c-1,c))),i,ROUND(IF(j<0.08,j+1,j),9),k,INDIRECT(ADDRESS(5,IF(ISODD(c),c,c+1))),o,ROUND(IF(k<0.09,k+1,k),9),AND(NOT(ISBLANK(j)),NOT(ISBLANK(k)),t>=i,t<o))
The cells in D5, and E5 are populated using a dropdown data validation which references the cells in B:B. But their values don't equal consistently unless I manually round the B:B values because it appears that data validation drop downs are rounded.
Below is an illustration of the cells being referenced. In this reference the formula would be applied to D8:E14.
A B C D E
2 | ||||
3 | ||||
4 | ||||
5 | 2:00 AM (dropdown) | 3:30 AM (dropdown) | ||
6 | ||||
7 | ||||
8 | 1:00 AM | |||
9 | 1:30 AM | |||
10 | 2:00 AM | Should be highlighted | Should be highlighted | |
11 | 2:30 AM | Should be highlighted | Should be highlighted | |
12 | 3:00 AM | Should be highlighted | Should be highlighted | |
13 | 3:30 AM | |||
14 | 4:00 AM |
No RepliesBe the first to reply