Chart with Dynamic Ranges
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
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.
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 | | | | |
---|
|
---|