Use Table Column references in Conditional Formatting Rules

[ilcaa]

i have a report, it generates a dynamic table, meaning the # of rows can change once its refreshed... I am trying to add conditional formatting, comparing 2 numbers in 2 different Tables.

i cannot select the cell because the location will change on refresh. I tried using Area_Sales[[#Totals],[% PAPER]] > Territory_Sales[[#Totals],[% PAPER]] but this doesnt work

can i use this type of reference? am I doing something wrong? if I place this is a cell is returns TRUE... solution?

 

[Fluff]

can i use this type of reference?

No you cant. You need to use normal cell references.

 

[Peter_SSs]

Is this what you are trying to do?

ilcaa.xlsm
	A	B	C	D
1	Area_Sales table			Territory_Sales table
2
3	% PAPER			% PAPER
4	55%			5%
5	2%			20%
6	57%			3%
7				16%
8				44%
9
Sheet1

Cell Formulas
Range	Formula
A6,D8	A6	=SUBTOTAL(109,[% PAPER])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6	Expression	=INDIRECT("Area_Sales[[#Totals],[% PAPER]]")>INDIRECT("Territory_Sales[[#Totals],[% PAPER]]")	text	NO

 

[ilcaa]

Is this what you are trying to do?

ilcaa.xlsm
	A	B	C	D
1	Area_Sales table			Territory_Sales table
2
3	% PAPER			% PAPER
4	55%			5%
5	2%			20%
6	57%			3%
7				16%
8				44%
9
Sheet1

Cell Formulas
Range	Formula
A6,D8	A6	=SUBTOTAL(109,[% PAPER])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6	Expression	=INDIRECT("Area_Sales[[#Totals],[% PAPER]]")>INDIRECT("Territory_Sales[[#Totals],[% PAPER]]")	text	NO

yes, i did not know i can use the indirect within the Conditional Formatting formula area.

i ended up making a sheet, and did the calc in a cell, it generates True or False, then within the Conditional formula, i just point to that cell and adjust formatting based on True or False. i did this for about 30 metrics, i think it makes it easier and everything is in 1 sheet. The conditonal formatting formula area is horrible small and annoying. but thanks for the tip

 

[Peter_SSs]

i ended up making a sheet, and did the calc in a cell, it generates True or False, then within the Conditional formula, i just point to that cell and adjust formatting based on True or False. i did this for about 30 metrics, i think it makes it easier and everything is in 1 sheet.

That does sound like a better plan.
However, you do now know that you can use structured references in Conditional Formatting.