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]

It may make sense to many, but not me!
Can you explain what part of the chart is going to be dynamic and what would cause a change?
I've already made the chart dynamic (with reference to Leader) with the drop down in cell A2 and the formulas inside the chart.

 

[awoohaw]

This is more dynamic but not a single cell dynamic crosstab. I updated the dummy data so that there is no manager to leader overlap. Since you have 365 you can work toward getting this all in one cell with Let and Lambda function. This requires you to have some overlap space at the bottom and also drag the count formulas down to the maximum number of managers per leader. So, if you have a leader with 20 managers and a leader with 5, the report of the leader with 5 will have 15 blank spaces under the crosstab chart. That can be fixed with more robust formulas using Lambda and Let statements.

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

Cell Formulas
Range	Formula
B4:D4	B4	=TRANSPOSE(UNIQUE(FILTER($A$20:$A$109,DAY($A$20:$A$109)=1)))
A5:A10	A5	=SORT(UNIQUE(FILTER($C$20:$C$109,$A$2=$B$20:$B$109)))
B5:D10	B5	=COUNTIFS( $C$20:$C$109, $A5, $B$20:$B$109, $A$2, $A$20:$A$109,">" & B$4, $A$20:$A$109,"<" & EOMONTH(B$4,0) )
Dynamic array formulas.

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