Conditional Formatting (CF Rule) Using INDIRECT Function

[zero269]

Hello,

I'm having some trouble getting a Conditional Formatting Rule to work using the INDIRECT function.

I'm using a formula within the Table for testing in column 'C'.
Column 'E' - outside the Table - was converted to use the INDIRECT function to work from outside the Table.
Columns A:B are formatted as Dates.

Both formulas produce the same results correctly, but using the formula in 'E' is not conditionally formatting the dates in 'A' that are greater than it's corresponding 'Due Date' in column 'B'.

I avoid using cell references when I can as my Table data may shift at times. Using INDIRECT prevents the formula from breaking if a new column is added or rearranged within the Table.

Any help on this would be greatly appreciated.

VBA Testing.xlsm
	A	B	C	D	E
1	CF Applied to:		Formula		CF Rule using INDIRECT
2	Test Date	Due Date	IF Test > Due
3	22-Jan	30-Jan			FALSE
4	31-Jan	30-Jan	Overdue		TRUE
5	24-Jan	30-Jan			FALSE
6	25-Jan	30-Jan			FALSE
7	26-Jan	30-Jan			FALSE
Indirect-Dates

Cell Formulas
Range	Formula
E3:E7	E3	=IF(ISNUMBER(INDIRECT("t_Indirect[Test Date]")),INDIRECT("t_Indirect[Test Date]") > INDIRECT("t_Indirect[Due Date]"),"")
C3:C7	C3	=IF(ISNUMBER([@[Test Date]]),IF([@[Test Date]] > [@[Due Date]],"Overdue",""),"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A7	Expression	=IF(ISNUMBER(INDIRECT("t_Indirect[Test Date]")),INDIRECT("t_Indirect[Test Date]") > INDIRECT("t_Indirect[Due Date]"),"")	text	NO

 

[Fluff]

How about

=AND(ISNUMBER(INDIRECT("t_Indirect[@[Test Date]]")),INDIRECT("t_Indirect[@[Test Date]]") > INDIRECT("t_Indirect[@[Due Date]]"))

 

[zero269]

How about

=AND(ISNUMBER(INDIRECT("t_Indirect[@[Test Date]]")),INDIRECT("t_Indirect[@[Test Date]]") > INDIRECT("t_Indirect[@[Due Date]]"))

Thanks for saving me again Fluff,

It works like a charm. I can't believe I still have such a hard time wrapping my head around what functions to use and when.

Is it safe to say that the IF function is not really - if ever - a viable CF Rule solution?

VBA Testing.xlsm
	A	B	C
2	Test Date	Due Date	IF Test > Due
3	22-Jan	30-Jan
4	31-Jan	30-Jan	Overdue
5	24-Jan	30-Jan
6	25-Jan	30-Jan
7	26-Jan	30-Jan
Indirect-Dates

Cell Formulas
Range	Formula
C3:C7	C3	=IF(ISNUMBER([@[Test Date]]),IF([@[Test Date]] > [@[Due Date]],"Overdue",""),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A7	Expression	=AND(ISNUMBER(INDIRECT("t_Indirect[@[Test Date]]")),INDIRECT("t_Indirect[@[Test Date]]") > INDIRECT("t_Indirect[@[Due Date]]"))	text	NO

 

[Fluff]

You do not need to use IF in CF as the CF just needs a true or false response.

 

[zero269]

You do not need to use IF in CF as the CF just needs a true or false response.

Excellent, thanks again, Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.