Dynamically adding and updating Conditional Formatting with a Named Range

[naquinn]

Hello,

I have a column of numbers with dates alongside. I can control a chart dynamically using the following named ranges:


This first one allows me to add to my dates (A), so the list grows over time
DATE
=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Next the DATE named range feeds into this INDEX and MATCH array so the chart axis can be controlled by two data validation drop down lists
DATE_CHART
=INDEX(DATE,MATCH(Sheet1!$D$3,DATE,0)):INDEX(DATE,MATCH(Sheet1!$E$3,DATE,0))

Same process for the totals column (B)
TOTALS
=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B:$B)-1,1)

...and
TOTALS_CHART
=INDEX(TOTALS,MATCH(Sheet1!$D$3,DATE,0)):INDEX(TOTALS,MATCH(Sheet1!$E$3,DATE,0))

By adding the DATE named range to the list section of the data validation box for cells D3 and E3 I can grow my list of data over time and dynamically control the chart with the two drop down list cells. The new entries in column A get added to the list via the COUNTA function.

Thankyou for reading this so far.... now this is the bit I have gotten stuck with.

I wanted to add a conditional formatting rule. Two scale colour to the B column. The idea being it acts like a kind of vertical mini heat map showing me at a glance the highs and lows on a scale across the different numbers in the column.

However, I'm having problems making it work dynamically, in a similar way to how the named ranges make my chart dynamic.

I have tried adding the TOTALS_CHART as a named range to the "applies to" box in the conditional formatting rules manager. Excel does accept the named range, and gives me a graded scale based on the array defined by cells D3 and E3 from how they are currently setup. So that does work, however, it doesn't update further when the D3 and E3 cells are changed.

Also, if I then add a new entry at the bottom of the list to columns A and B (a new date and totals number) the conditional formatting doesn't pick that up at all, even though the TOTALS_CHART named range has been entered into the conditional formatting manager for column B.

If anyone might be able to help, or point me in the right direction, I'd be very grateful. Been playing around with it all day and I'm not sure what else to try.

Many thanks

Conditional Formatting.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	DATE	TOTALS		START DATE	END DATE
3	21/01/2021	163,015		28/01/2021	17/02/2021
4	22/01/2021	190,423
5	23/01/2021	133,823
6	24/01/2021	107,328
7	25/01/2021	74,433
8	26/01/2021	-28,331
9	27/01/2021	-70,584
10	28/01/2021	-82,001
11	29/01/2021	-68,808
12	30/01/2021	-80,825
13	31/01/2021	-115,426
14	01/02/2021	-78,949
15	02/02/2021	-21,417
16	03/02/2021	-7,268
17	04/02/2021	50,213
18	05/02/2021	74,295
19	06/02/2021	100,137
20	07/02/2021	112,096
21	08/02/2021	-7,081
22	09/02/2021	-7,281
23	10/02/2021	370
24	11/02/2021	12,060
25	12/02/2021	-9,341
26	13/02/2021	-9,503
27	14/02/2021	-20,139
28	15/02/2021	20,837
29	16/02/2021	38,246
30	17/02/2021	15,613
31
32
33
Sheet1

Cell Formulas
Range	Formula
A4:A30	A4	=SUM(A3+1)

Named Ranges
Name	Refers To	Cells
DATE	=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A)-1,1)	A4
DATE_CHART	=INDEX(DATE,MATCH(Sheet1!$D$3,DATE,0)):INDEX(DATE,MATCH(Sheet1!$E$3,DATE,0))	A11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B10:B30	Other Type	Color scale		NO

Cells with Data Validation
Cell	Allow	Criteria
D3:E3	List	=DATE