Hello all,
I'm attempting some relatively complex conditional formatting. I've broken it down into several rules, but I keep running in a brick wall with the use of the two referenced functions within conditional formatting.
Here is one rule for the the conditional formatting of one cell:
OffsetV and OffsetH are named ranges (single cell). Cell A3 is the cell this conditional format is contained within (no absolute references because I need the cell address to carry across the spreadsheet). Cell A1 contains the date value I'm checking against. The result of the OFFSET() function is the date I need to check against the date value in Cell A1.
These formula work perfectly in any cell on the spreadsheet, but fail miserably when input into conditional formatting. Through various experimentation, I found that if I remove the Row() and Column() functions and replace them with the result (i.e. Replace Row(A3) with 3), the conditional formatting works fine. However, if I use the Row() and Column() functions in a simpler conditional format (i.e. IsOdd(Row()), it works fine.
Any idea why it might be failing only within complex conditional formatting?
Thanks,
Ed
I'm attempting some relatively complex conditional formatting. I've broken it down into several rules, but I keep running in a brick wall with the use of the two referenced functions within conditional formatting.
Here is one rule for the the conditional formatting of one cell:
Code:
=IF(OFFSET(A3,INT((ROW(A3)-2)/OffsetV)*OffsetV-(ROW(A3)-2-1),INT(COLUMN(A3)/OffsetH)*OffsetH+(OffsetH-COLUMN(A3)))<A1,TRUE,FALSE)
OffsetV and OffsetH are named ranges (single cell). Cell A3 is the cell this conditional format is contained within (no absolute references because I need the cell address to carry across the spreadsheet). Cell A1 contains the date value I'm checking against. The result of the OFFSET() function is the date I need to check against the date value in Cell A1.
These formula work perfectly in any cell on the spreadsheet, but fail miserably when input into conditional formatting. Through various experimentation, I found that if I remove the Row() and Column() functions and replace them with the result (i.e. Replace Row(A3) with 3), the conditional formatting works fine. However, if I use the Row() and Column() functions in a simpler conditional format (i.e. IsOdd(Row()), it works fine.
Any idea why it might be failing only within complex conditional formatting?
Thanks,
Ed