Chart with Dynamic Ranges

[zinah]

Hello,

I was trying to paste a mini sheet here but got error message (as showing in screenshot attached). I have a drop-down list that can pull different data, some data has blanks or 0s. What I need to create is dynamic ranges for this data to be presented in a chart (like bar chart or column chart). Can anyone help?

Dropdown list that has all Top Leader Names
DEF
Manager Name	Jan-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	May-2024	Jun-2024	Dec-2024	Aug-2025
BB												1
AA	2	1		1	1
CC												1
DD	9											7
EE												1
FF
GG		1	1			2	1
HH						1										3
RR	9	2		1					1	15		6			1		2
YY		1					1	1
	20	5	1	2	1	3	2	1	1	15	0	16	0	0	1	3	2

Thank you,
Zina

 

[awoohaw]

What is the relationship of the Top Leader Names to the data?
Is there other data that goes into the reporting?

 

[zinah]

What is the relationship of the Top Leader Names to the data?
Is there other data that goes into the reporting?

The numbers of each month and leaders names are dynamic, whenever I change the leader name, the manager name will change and numbers of each month will change too.

 

[awoohaw]

The numbers of each month and leaders names are dynamic, whenever I change the leader name, the manager name will change and numbers of each month will change too.

Do you have sample (anonymized) data?

 

[zinah]

Do you have sample (anonymized) data?

Yes, here's the sample data:

Dynamic Ranges Chart.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Dropdown list that has all Top Leader Names
2	DEF
3
4
5	Manager Name	Jan-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	May-2024	Jun-2024	Dec-2024	Aug-2025
6	BB												1
7	AA	2	1		1	1
8	CC												1
9	DD	9											7
10	EE												1
11	FF
12	GG		1	1			2	1
13	HH						1										3
14	RR	9	2		1					1	15		6			1		2
15	YY		1					1	1
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32		20	5	1	2	1	3	2	1	1	15	0	16	0	0	1	3	2
Sheet1

Cell Formulas
Range	Formula
B32:R32	B32	=SUM(B6:B31)

Cells with Data Validation
Cell	Allow	Criteria
A2	List	ABC, DEF, REE, ZNA

 

[awoohaw]

Please get the xl2BB tool. It took me quite a while to build a sample data set. I don't think the chart above is your source data (My sample data are in A20:C109). But, please let me know if this works for you? It can be made more elegantly with Let and Lambda functions.

WorkBook1.xlsx
	A	B	C	D	E	F	G	H
1	Dropdown list that has all Top Leader Names						LEADER
2	DEF						ABC
3							DEF
4	Manager Name	Jan-23	Feb-23	Mar-23			REE
5	BB	0	1	2			ZNA
6	AA	2	0	0
7	CC	0	0	0
8	DD	0	0	1
9	EE	0	0	2
10	FF	0	0	0
11	GG	0	0	0
12	HH	1	0	0
13	RR	0	0	0
14	YY	0	0	0
15
16
17
18
19	DATE	LEADER	MANAGER
20	1/1/2023	ABC	AA
21	1/2/2023	DEF	AA
22	1/3/2023	REE	AA
23	1/4/2023	ZNA	CC
24	1/5/2023	ABC	BB
25	1/6/2023	DEF	AA
26	1/7/2023	REE	DD
27	1/8/2023	ZNA	FF
28	1/9/2023	ABC	BB
29	1/10/2023	DEF
30	1/11/2023	REE
31	1/12/2023	ZNA	BB
32	1/13/2023	ABC	YY
33	1/14/2023	DEF
34	1/15/2023	REE
35	1/16/2023	ZNA
36	1/17/2023	ABC
37	1/18/2023	DEF	HH
38	1/19/2023	REE
39	1/20/2023	ZNA
40	1/21/2023	ABC
41	1/22/2023	DEF
42	1/23/2023	REE	HH
43	1/24/2023	ZNA
44	1/25/2023	ABC
45	1/26/2023	DEF
46	1/27/2023	REE
47	1/28/2023	ZNA	HH
48	1/29/2023	ABC
49	1/30/2023	DEF
50	1/31/2023	REE
51	2/1/2023	ZNA
52	2/2/2023	ABC
53	2/3/2023	DEF
54	2/4/2023	REE
55	2/5/2023	ZNA
56	2/6/2023	ABC	DD
57	2/7/2023	DEF
58	2/8/2023	REE	AA
59	2/9/2023	ZNA
60	2/10/2023	ABC	AA
61	2/11/2023	DEF
62	2/12/2023	REE	HH
63	2/13/2023	ZNA
64	2/14/2023	ABC	AA
65	2/15/2023	DEF	BB
66	2/16/2023	REE
67	2/17/2023	ZNA
68	2/18/2023	ABC	BB
69	2/19/2023	DEF
70	2/20/2023	REE	AA
71	2/21/2023	ZNA
72	2/22/2023	ABC	YY
73	2/23/2023	DEF
74	2/24/2023	REE	CC
75	2/25/2023	ZNA
76	2/26/2023	ABC
77	2/27/2023	DEF
78	2/28/2023	REE	FF
79	3/1/2023	ZNA
80	3/2/2023	ABC
81	3/3/2023	DEF
82	3/4/2023	REE
83	3/5/2023	ZNA	FF
84	3/6/2023	ABC
85	3/7/2023	DEF
86	3/8/2023	REE
87	3/9/2023	ZNA	AA
88	3/10/2023	ABC
89	3/11/2023	DEF
90	3/12/2023	REE	BB
91	3/13/2023	ZNA
92	3/14/2023	ABC
93	3/15/2023	DEF	BB
94	3/16/2023	REE	EE
95	3/17/2023	ZNA	EE
96	3/18/2023	ABC	EE
97	3/19/2023	DEF	EE
98	3/20/2023	REE	EE
99	3/21/2023	ZNA	EE
100	3/22/2023	ABC	EE
101	3/23/2023	DEF	EE
102	3/24/2023	REE	DD
103	3/25/2023	ZNA	GG
104	3/26/2023	ABC	RR
105	3/27/2023	DEF	DD
106	3/28/2023	REE	CC
107	3/29/2023	ZNA	YY
108	3/30/2023	ABC	YY
109	3/31/2023	DEF	BB
110
Sheet1

Cell Formulas
Range	Formula
B4:D4	B4	=DATE(2023,SEQUENCE(1,3,1,1),1)
B5:C14	B5	=SUMPRODUCT(--(Sheet1!$A$20:$A$109<=EOMONTH(B$4,0))* --(Sheet1!$A$20:$A$109>=B$4)* --($A$2 = Sheet1!$B$20:$B$109)*--($A5=Sheet1!$C$20:$C$109))
D5:D14	D5	=SUMPRODUCT( --(Sheet1!$A$20:$A$109>=D$4), --(Sheet1!$A$20:$A$109<=EOMONTH(D$4,0)), --($A$2 = Sheet1!$B$20:$B$109), --($A5=Sheet1!$C$20:$C$109))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2:A3	List	=$G$2:$G$5

 

[zinah]

Please get the xl2BB tool. It took me quite a while to build a sample data set. I don't think the chart above is your source data (My sample data are in A20:C109). But, please let me know if this works for you? It can be made more elegantly with Let and Lambda functions.

WorkBook1.xlsx
	A	B	C	D	E	F	G	H
1	Dropdown list that has all Top Leader Names						LEADER
2	DEF						ABC
3							DEF
4	Manager Name	Jan-23	Feb-23	Mar-23			REE
5	BB	0	1	2			ZNA
6	AA	2	0	0
7	CC	0	0	0
8	DD	0	0	1
9	EE	0	0	2
10	FF	0	0	0
11	GG	0	0	0
12	HH	1	0	0
13	RR	0	0	0
14	YY	0	0	0
15
16
17
18
19	DATE	LEADER	MANAGER
20	1/1/2023	ABC	AA
21	1/2/2023	DEF	AA
22	1/3/2023	REE	AA
23	1/4/2023	ZNA	CC
24	1/5/2023	ABC	BB
25	1/6/2023	DEF	AA
26	1/7/2023	REE	DD
27	1/8/2023	ZNA	FF
28	1/9/2023	ABC	BB
29	1/10/2023	DEF
30	1/11/2023	REE
31	1/12/2023	ZNA	BB
32	1/13/2023	ABC	YY
33	1/14/2023	DEF
34	1/15/2023	REE
35	1/16/2023	ZNA
36	1/17/2023	ABC
37	1/18/2023	DEF	HH
38	1/19/2023	REE
39	1/20/2023	ZNA
40	1/21/2023	ABC
41	1/22/2023	DEF
42	1/23/2023	REE	HH
43	1/24/2023	ZNA
44	1/25/2023	ABC
45	1/26/2023	DEF
46	1/27/2023	REE
47	1/28/2023	ZNA	HH
48	1/29/2023	ABC
49	1/30/2023	DEF
50	1/31/2023	REE
51	2/1/2023	ZNA
52	2/2/2023	ABC
53	2/3/2023	DEF
54	2/4/2023	REE
55	2/5/2023	ZNA
56	2/6/2023	ABC	DD
57	2/7/2023	DEF
58	2/8/2023	REE	AA
59	2/9/2023	ZNA
60	2/10/2023	ABC	AA
61	2/11/2023	DEF
62	2/12/2023	REE	HH
63	2/13/2023	ZNA
64	2/14/2023	ABC	AA
65	2/15/2023	DEF	BB
66	2/16/2023	REE
67	2/17/2023	ZNA
68	2/18/2023	ABC	BB
69	2/19/2023	DEF
70	2/20/2023	REE	AA
71	2/21/2023	ZNA
72	2/22/2023	ABC	YY
73	2/23/2023	DEF
74	2/24/2023	REE	CC
75	2/25/2023	ZNA
76	2/26/2023	ABC
77	2/27/2023	DEF
78	2/28/2023	REE	FF
79	3/1/2023	ZNA
80	3/2/2023	ABC
81	3/3/2023	DEF
82	3/4/2023	REE
83	3/5/2023	ZNA	FF
84	3/6/2023	ABC
85	3/7/2023	DEF
86	3/8/2023	REE
87	3/9/2023	ZNA	AA
88	3/10/2023	ABC
89	3/11/2023	DEF
90	3/12/2023	REE	BB
91	3/13/2023	ZNA
92	3/14/2023	ABC
93	3/15/2023	DEF	BB
94	3/16/2023	REE	EE
95	3/17/2023	ZNA	EE
96	3/18/2023	ABC	EE
97	3/19/2023	DEF	EE
98	3/20/2023	REE	EE
99	3/21/2023	ZNA	EE
100	3/22/2023	ABC	EE
101	3/23/2023	DEF	EE
102	3/24/2023	REE	DD
103	3/25/2023	ZNA	GG
104	3/26/2023	ABC	RR
105	3/27/2023	DEF	DD
106	3/28/2023	REE	CC
107	3/29/2023	ZNA	YY
108	3/30/2023	ABC	YY
109	3/31/2023	DEF	BB
110
Sheet1

Cell Formulas
Range	Formula
B4:D4	B4	=DATE(2023,SEQUENCE(1,3,1,1),1)
B5:C14	B5	=SUMPRODUCT(--(Sheet1!$A$20:$A$109<=EOMONTH(B$4,0))* --(Sheet1!$A$20:$A$109>=B$4)* --($A$2 = Sheet1!$B$20:$B$109)*--($A5=Sheet1!$C$20:$C$109))
D5:D14	D5	=SUMPRODUCT( --(Sheet1!$A$20:$A$109>=D$4), --(Sheet1!$A$20:$A$109<=EOMONTH(D$4,0)), --($A$2 = Sheet1!$B$20:$B$109), --($A5=Sheet1!$C$20:$C$109))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2:A3	List	=$G$2:$G$5

I think you didn't get my point, what I need is to build a chart with dynamic ranges, where if I change the name of the leader, then the dynamic ranges of the chart will be changed according to the data and skip the blanks. I added data along with formulas to let you know what I need exactly:

Dynamic Ranges Chart.xlsx
	A
2	ZNA
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	ABC, DEF, REE, ZNA

 

[zinah]

I don't think you got what I wanted. What I want is to have dynamic ranges for the bar chart where if I change the leader name and populate the managers under each leader the ranges will be considering only the populated managers names along with their data. i.e. some leaders have 5 managers and other have 20+, this may cause blank ranges/cells. Here's the sample data:

Dynamic Ranges Chart.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Dropdown list that has all Top Leader Names
2	ZNA																Manager Name	Leader	Date	Formatted Date
3																	BB	REE	1/2/2023	Jan-2023
4																	AA	REE	1/3/2023	Jan-2023
5	Manager Name	Jan-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023				CC	REE	1/4/2023	Jan-2023
6	4Q			3		1											DD	REE	1/5/2023	Jan-2023
7	4R			3		1											EE	REE	1/6/2023	Jan-2023
8	4S			4													FF	REE	1/7/2023	Jan-2023
9	4T			4													GG	REE	1/8/2023	Jan-2023
10	4V			4													HH	REE	1/9/2023	Jan-2023
11	4Y			3				1									2G	ABC	1/18/2023	Jan-2023
12	4Z			4													2H	ABC	1/19/2023	Jan-2023
13	4X			6													2I	ABC	1/20/2023	Jan-2023
14																	2J	ABC	1/21/2023	Jan-2023
15																	2K	ABC	1/22/2023	Jan-2023
16																	2L	ABC	1/23/2023	Jan-2023
17																	2M	ABC	1/24/2023	Jan-2023
18																	2N	ABC	1/25/2023	Jan-2023
19																	2O	ABC	1/26/2023	Jan-2023
20																	2P	ABC	1/27/2023	Jan-2023
21																	3V	DEF	3/26/2023	Mar-2023
22																	3Y	DEF	3/27/2023	Mar-2023
23																	3Z	DEF	3/28/2023	Mar-2023
24																	3X	DEF	3/29/2023	Mar-2023
25																	4Q	ZNA	3/30/2023	Mar-2023
26																	4R	ZNA	3/31/2023	Mar-2023
27																	4S	ZNA	3/3/2023	Mar-2023
28																	4T	ZNA	3/4/2023	Mar-2023
29																	4V	ZNA	3/5/2023	Mar-2023
30		0	0	31	0	2	0	1	0	0	0	0	0				4Y	ZNA	3/6/2023	Mar-2023
31																	4Z	ZNA	3/7/2023	Mar-2023
32																	4X	ZNA	3/8/2023	Mar-2023
33																	BB	REE	1/2/2023	Jan-2023
34																	AA	REE	1/3/2023	Jan-2023
35																	CC	REE	1/4/2023	Jan-2023
36																	DD	REE	1/5/2023	Jan-2023
37																	EE	REE	1/6/2023	Jan-2023
38																	FF	REE	1/7/2023	Jan-2023
39																	GG	REE	1/8/2023	Jan-2023
40																	HH	REE	1/9/2023	Jan-2023
41																	RR	REE	1/10/2023	Jan-2023
42																	YY	REE	1/11/2023	Jan-2023
43																	2A	ABC	1/12/2023	Jan-2023
44																	2B	ABC	1/13/2023	Jan-2023
45																	2C	ABC	1/14/2023	Jan-2023
46																	2D	ABC	1/15/2023	Jan-2023
47																	3Z	DEF	3/28/2023	Mar-2023
48																	3X	DEF	3/29/2023	Mar-2023
49																	4Q	ZNA	3/30/2023	Mar-2023
50																	4R	ZNA	3/31/2023	Mar-2023
51																	4S	ZNA	3/3/2023	Mar-2023
52																	4T	ZNA	3/4/2023	Mar-2023
53																	4V	ZNA	3/5/2023	Mar-2023
54																	4Y	ZNA	3/6/2023	Mar-2023
55																	4Z	ZNA	3/7/2023	Mar-2023
56																	4X	ZNA	3/8/2023	Mar-2023
57																	4X	ZNA	3/8/2023	Mar-2023
58																	BB	REE	1/2/2023	Jan-2023
59																	AA	REE	1/3/2023	Jan-2023
60																	CC	REE	1/4/2023	Jan-2023
61																	DD	REE	1/5/2023	Jan-2023
62																	2K	ABC	1/22/2023	Jan-2023
63																	2L	ABC	1/23/2023	Jan-2023
64																	2M	ABC	1/24/2023	Jan-2023
65																	2N	ABC	1/25/2023	Jan-2023
66																	2O	ABC	1/26/2023	Jan-2023
67																	2P	ABC	1/27/2023	Jan-2023
68																	2Q	ABC	1/28/2023	Jan-2023
69																	2R	ABC	1/29/2023	Jan-2023
70																	4Q	ZNA	5/30/2023	May-2023
71																	4R	ZNA	5/31/2023	May-2023
72																	4S	ZNA	3/3/2023	Mar-2023
73																	4T	ZNA	3/4/2023	Mar-2023
74																	4V	ZNA	3/5/2023	Mar-2023
75																	4Y	ZNA	7/6/2023	Jul-2023
76																	4Z	ZNA	3/7/2023	Mar-2023
77																	4X	ZNA	3/8/2023	Mar-2023
78																	4X	ZNA	3/8/2023	Mar-2023
79																	BB	REE	1/2/2023	Jan-2023
80																	AA	REE	1/3/2023	Jan-2023
81																	CC	REE	8/4/2023	Aug-2023
82																	DD	REE	8/5/2023	Aug-2023
83																	2G	ABC	1/18/2023	Jan-2023
84																	2H	ABC	4/19/2023	Apr-2023
85																	2I	ABC	4/19/2023	Apr-2023
86																	2J	ABC	1/21/2023	Jan-2023
87																	2K	ABC	1/22/2023	Jan-2023
88																	2L	ABC	1/23/2023	Jan-2023
89																	2M	ABC	1/24/2023	Jan-2023
90																	2N	ABC	4/19/2023	Apr-2023
91																	2O	ABC	1/26/2023	Jan-2023
92																	2P	ABC	1/27/2023	Jan-2023
93																	2Q	ABC	4/19/2023	Apr-2023
94																	2R	ABC	1/29/2023	Jan-2023
95																	2S	ABC	1/30/2023	Jan-2023
96																	3S	DEF	3/24/2023	Mar-2023
97																	3T	DEF	3/25/2023	Mar-2023
98																	3V	DEF	3/26/2023	Mar-2023
99																	3Y	DEF	3/27/2023	Mar-2023
100																	3Z	DEF	3/28/2023	Mar-2023
101																	3X	DEF	3/29/2023	Mar-2023
102																	4Q	ZNA	3/30/2023	Mar-2023
103																	4R	ZNA	3/31/2023	Mar-2023
104																	4S	ZNA	3/3/2023	Mar-2023
105																	4T	ZNA	3/4/2023	Mar-2023
106																	4V	ZNA	3/5/2023	Mar-2023
107																	4Y	ZNA	3/6/2023	Mar-2023
108																	4Z	ZNA	3/7/2023	Mar-2023
109																	4X	ZNA	3/8/2023	Mar-2023
Sheet1

Cell Formulas
Range	Formula
A6:A13	A6	=UNIQUE(FILTER($Q$3:$Q$32,$R$3:$R$32=$A$2))
B6:M29	B6	=COUNTIFS($Q:$Q,$A6,$T:$T,B$5)
B30:M30	B30	=SUM(B6:B29)
T3:T109	T3	=TEXT(S3,"MMM-YYYY")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2	List	ABC, DEF, REE, ZNA

 

[awoohaw]

I'm still confused.

How do you get a value of 3 for March for ZNA for manager Q3? You have a crosstab report chart, not data. Crosstab charts have source data. To attempt your request I had to make data to create that dynamic crosstab chart.

Cell A2 has a drop down so you can filter and make the crosstab dynamic. As far as blanks, I'm not sure what you mean - if you are asking for completely blank rows (no data during any time frame) for the MANAGERS that can be accomplished.

The Sample Data I gave you had multiple Managers with Same Leaders. If that never happens the report I did should still work, unless I am completely off of understanding what you want.

 

[zinah]

I'm still confused.

How do you get a value of 3 for March for ZNA for manager Q3? You have a crosstab report chart, not data. Crosstab charts have source data. To attempt your request I had to make data to create that dynamic crosstab chart.

Cell A2 has a drop down so you can filter and make the crosstab dynamic. As far as blanks, I'm not sure what you mean - if you are asking for completely blank rows (no data during any time frame) for the MANAGERS that can be accomplished.

The Sample Data I gave you had multiple Managers with Same Leaders. If that never happens the report I did should still work, unless I am completely off of understanding what you want.

Sorry for the confusion. You're missing my point, my focus is to create dynamic ranges, I've seen somewhere where they used offset formula in Name Manager from Formulas Tab in excel and they replaced it the name they created in the chart ranges, this is what I need. my focus is not the data, it's dynamic ranges for chart. Does than make sense?