Forum Discussion

DevinDevin's avatar
DevinDevin
Copper Contributor
Jun 25, 2023

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.

SAMPLE

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    
81:00 AM   
91:30 AM   
102:00 AM Should be highlightedShould be highlighted
112:30 AM Should be highlightedShould be highlighted
123:00 AM Should be highlightedShould be highlighted
133:30 AM   
144:00 AM  
No RepliesBe the first to reply

Resources