Graph Range

[vmjan02]

I have this graph and the range will be dynamic, i tried with name manager with Name =OFFSET(Graphs!$A$7,,,COUNTIF(Graphs!$A$7:$A$16,"<>")) and Value =OFFSET(Graphs!$B$7:$G$16,,,COUNTIF(Graphs!$B$7:$G$16,"<>")) but no luck any best way to fix this dynamic range,

in case there is a vba will also work for me.

 

[Kaper]

If I understand well the idea - to show data from extra rows when they are added, you may put your data in excel table (Insert->Tables->Table). Then recreate the graph based on a table. And then series will be automatically adopted to all rows as you add new row to a table.

 

[awoohaw]

What version of excel are you using for this workbook?
It would be a better help if you provided some of the total data instead of what the chart source data.
Thanks in advance.

 

[vmjan02]

sorry for the delay in response

@Kaper
I tried this but no luck

@awoohaw
Using 2016. version

 

[awoohaw]

@awoohaw
Using 2016. version

please provide some data. In a table or ideally a xl2bb mini sheet (see link below). Images are not helpful as it means the forum needs to completely type the data into their own worksheets which can lead to errors

 

[felixstraube]

Here is a simplified example:
(Please ignore the concat formula in column F, I just added it so the named ranges are included in the XL2BB code)

Book1
	A	B	C	D	E	F
1
2
3
4
5
6		W-21	W-22	Total
7	Name1	21	38	59		Name1213859
8	Name2	23	28	51
9	Name3	25	5	30
10
11
12
13
14
15
16
Graphs

Cell Formulas
Range	Formula
F7	F7	=CONCAT(A7:D7)
D7:D9	D7	=SUM(B7:C7)

Named Ranges
Name	Refers To	Cells
Names	=OFFSET(Graphs!$A$7,0,0,COUNTA(Graphs!$A$7:$A$16))	F7
Total	=OFFSET(Graphs!$D$7,0,0,COUNTA(Graphs!$A$7:$A$16))	F7
W_21	=OFFSET(Graphs!$B$7,0,0,COUNTA(Graphs!$A$7:$A$16))	F7, D7
W_22	=OFFSET(Graphs!$C$7,0,0,COUNTA(Graphs!$A$7:$A$16))	F7, D7

For it to work you have to include the sheet name in the the graph data source when you reference the named ranges (if the scope of the named range is Workbook, it automatically replaces it with the book name).

Now if you add another line it updates as expected:

 

[awoohaw]

I've tried to create a data set that may help. The data is in an xl2bb minisheet below this image

Here is the xl2bb, (charts don't show up, you'll have to build it yourself, but this has all the data you need for it.
Please note, this is dynamic by changing the date in cell H1.
(I had to post this in 2 sections, first is rows 1-150, then 151 to 376.

Book1
	B	C	D	E	F	G	H	I	J	K
1	Titla:	Calls per FDT for wk-1 to wk-5			Report Start Week:		Wed 2024-01-03
2					(weeks begin on Mondays)
3					FDT	wk-1	wk-2	wk-3	wk-4	wk-5
4	wk-1	Sahafah	0		Sahafah	59	31	43	54	27
5	wk-1	Sahafah	0		Yasmeen	56	32	46	59	37
6	wk-1	Sahafah	1		Malqa	40	40	48	34	54
7	wk-1	Yasmeen	3		Total	155	103	137	147	118
8	wk-1	Yasmeen	6
9	wk-1	Yasmeen	0
10	wk-1	Malqa	4
11	wk-1	Sahafah	5
12	wk-1	Sahafah	4
13	wk-1	Sahafah	3
14	wk-1	Yasmeen	3
15	wk-1	Sahafah	7
16	wk-1	Yasmeen	0
17	wk-1	Malqa	7
18	wk-1	Malqa	6
19	wk-1	Sahafah	5
20	wk-1	Malqa	1
21	wk-1	Yasmeen	4
22	wk-2	Malqa	0
23	wk-1	Sahafah	4
24	wk-1	Malqa	7
25	wk-1	Malqa	1
26	wk-1	Sahafah	7
27	wk-1	Sahafah	2
28	wk-1	Yasmeen	8
29	wk-1	Yasmeen	5
30	wk-1	Malqa	1
31	wk-1	Yasmeen	0
32	wk-2	Yasmeen	5
33	wk-1	Yasmeen	7
34	wk-1	Yasmeen	5
35	wk-1	Malqa	1
36	wk-1	Sahafah	8
37	wk-1	Yasmeen	4
38	wk-1	Yasmeen	4
39	wk-1	Malqa	4
40	wk-1	Sahafah	6
41	wk-2	Malqa	1
42	wk-2	Malqa	1
43	wk-3	Malqa	0
44	wk-1	Sahafah	1
45	wk-3	Sahafah	1
46	wk-3	Yasmeen	2
47	wk-3	Malqa	3
48	wk-1	Malqa	1
49	wk-2	Yasmeen	7
50	wk-3	Sahafah	8
51	wk-2	Yasmeen	3
52	wk-2	Yasmeen	2
53	wk-1	Malqa	4
54	wk-1	Yasmeen	2
55	wk-1	Yasmeen	0
56	wk-2	Sahafah	2
57	wk-2	Yasmeen	1
58	wk-2	Sahafah	1
59	wk-2	Malqa	5
60	wk-2	Yasmeen	7
61	wk-2	Malqa	2
62	wk-1	Malqa	3
63	wk-4	Sahafah	5
64	wk-4	Yasmeen	6
65	wk-4	Sahafah	3
66	wk-2	Malqa	6
67	wk-1	Sahafah	2
68	wk-4	Malqa	6
69	wk-2	Malqa	0
70	wk-4	Sahafah	2
71	wk-1	Sahafah	1
72	wk-2	Sahafah	6
73	wk-2	Sahafah	8
74	wk-1	Sahafah	3
75	wk-3	Sahafah	3
76	wk-1	Yasmeen	5
77	wk-3	Malqa	7
78	wk-4	Yasmeen	1
79	wk-4	Yasmeen	2
80	wk-4	Sahafah	3
81	wk-4	Yasmeen	5
82	wk-3	Sahafah	0
83	wk-3	Yasmeen	0
84	wk-5	Yasmeen	4
85	wk-4	Malqa	1
86	wk-4	Yasmeen	8
87	wk-3	Yasmeen	1
88	wk-2	Malqa	2
89	wk-5	Yasmeen	6
90	wk-2	Sahafah	6
91	wk-2	Yasmeen	5
92	wk-3	Sahafah	5
93	wk-5	Malqa	7
94	wk-3	Malqa	6
95	wk-3	Malqa	8
96	wk-4	Sahafah	0
97	wk-3	Sahafah	2
98	wk-3	Malqa	5
99	wk-5	Yasmeen	6
100	wk-4	Yasmeen	2
101	wk-4	Malqa	5
102	wk-6	Sahafah	1
103	wk-2	Malqa	4
104	wk-6	Yasmeen	2
105	wk-4	Malqa	8
106	wk-6	Yasmeen	8
107	wk-3	Yasmeen	0
108	wk-5	Malqa	7
109	wk-2	Malqa	0
110	wk-2	Malqa	8
111	wk-6	Sahafah	6
112	wk-3	Sahafah	1
113	wk-2	Malqa	7
114	wk-4	Malqa	0
115	wk-5	Malqa	7
116	wk-4	Malqa	6
117	wk-6	Yasmeen	4
118	wk-5	Malqa	5
119	wk-3	Sahafah	8
120	wk-5	Sahafah	4
121	wk-3	Malqa	3
122	wk-2	Malqa	4
123	wk-2	Yasmeen	1
124	wk-7	Yasmeen	4
125	wk-2	Yasmeen	1
126	wk-3	Malqa	7
127	wk-3	Yasmeen	7
128	wk-4	Sahafah	6
129	wk-7	Sahafah	7
130	wk-2	Sahafah	8
131	wk-3	Malqa	4
132	wk-3	Yasmeen	4
133	wk-5	Yasmeen	4
134	wk-6	Sahafah	4
135	wk-7	Yasmeen	1
136	wk-5	Yasmeen	8
137	wk-4	Yasmeen	5
138	wk-3	Malqa	5
139	wk-7	Yasmeen	1
140	wk-7	Yasmeen	2
141	wk-7	Sahafah	8
142	wk-4	Yasmeen	8
143	wk-8	Sahafah	8
144	wk-9	Yasmeen	0
145	wk-6	Malqa	2
146	wk-7	Sahafah	4
147	wk-5	Sahafah	5
148	wk-4	Yasmeen	0
149	wk-9	Sahafah	6
150	wk-4	Malqa	0
Sheet1

Cell Formulas
Range	Formula
C1	C1	="Calls per FDT for "& G3 & " to " & K3
G3:K3	G3	="wk-"&WEEKNUM($H$1)+SEQUENCE(,5,0,1)
F4:F6	F4	=UNIQUE(C4:C376)
G4:K6	G4	=SUMIFS($D$4:$D$376,$B$4:$B$376,G$3,$C$4:$C$376,$F4)
G7:K7	G7	=SUM(G4:G6)
Dynamic array formulas.

Book1
	B	C	D
151	wk-9	Yasmeen	0
152	wk-8	Sahafah	4
153	wk-4	Yasmeen	1
154	wk-7	Yasmeen	2
155	wk-3	Yasmeen	4
156	wk-8	Yasmeen	8
157	wk-9	Malqa	3
158	wk-6	Yasmeen	0
159	wk-6	Sahafah	8
160	wk-4	Sahafah	7
161	wk-4	Sahafah	4
162	wk-4	Yasmeen	6
163	wk-10	Yasmeen	4
164	wk-3	Yasmeen	6
165	wk-10	Yasmeen	2
166	wk-6	Malqa	6
167	wk-10	Yasmeen	3
168	wk-10	Yasmeen	1
169	wk-7	Malqa	2
170	wk-3	Malqa	0
171	wk-5	Malqa	6
172	wk-4	Yasmeen	6
173	wk-6	Yasmeen	7
174	wk-6	Yasmeen	5
175	wk-3	Yasmeen	2
176	wk-7	Yasmeen	6
177	wk-10	Malqa	8
178	wk-10	Malqa	0
179	wk-7	Sahafah	5
180	wk-10	Sahafah	3
181	wk-8	Malqa	5
182	wk-8	Sahafah	8
183	wk-7	Malqa	1
184	wk-11	Yasmeen	4
185	wk-11	Sahafah	0
186	wk-11	Yasmeen	0
187	wk-6	Yasmeen	8
188	wk-3	Yasmeen	8
189	wk-7	Malqa	8
190	wk-7	Malqa	6
191	wk-3	Yasmeen	6
192	wk-11	Malqa	4
193	wk-8	Yasmeen	1
194	wk-4	Malqa	0
195	wk-11	Sahafah	4
196	wk-11	Sahafah	3
197	wk-8	Malqa	0
198	wk-11	Sahafah	7
199	wk-4	Yasmeen	1
200	wk-11	Yasmeen	7
201	wk-8	Malqa	8
202	wk-3	Sahafah	7
203	wk-8	Sahafah	4
204	wk-8	Sahafah	7
205	wk-3	Yasmeen	6
206	wk-12	Yasmeen	0
207	wk-9	Yasmeen	3
208	wk-8	Yasmeen	2
209	wk-6	Malqa	7
210	wk-3	Sahafah	6
211	wk-6	Sahafah	4
212	wk-5	Malqa	7
213	wk-8	Sahafah	7
214	wk-8	Malqa	0
215	wk-11	Malqa	5
216	wk-6	Yasmeen	5
217	wk-9	Sahafah	3
218	wk-6	Sahafah	5
219	wk-3	Sahafah	2
220	wk-11	Sahafah	0
221	wk-8	Sahafah	2
222	wk-5	Sahafah	8
223	wk-7	Yasmeen	8
224	wk-7	Yasmeen	6
225	wk-13	Malqa	5
226	wk-10	Malqa	3
227	wk-7	Sahafah	5
228	wk-13	Malqa	8
229	wk-7	Yasmeen	5
230	wk-5	Malqa	0
231	wk-4	Sahafah	2
232	wk-5	Malqa	5
233	wk-12	Sahafah	5
234	wk-12	Yasmeen	5
235	wk-5	Sahafah	8
236	wk-10	Sahafah	2
237	wk-4	Yasmeen	7
238	wk-10	Malqa	4
239	wk-10	Sahafah	8
240	wk-4	Yasmeen	1
241	wk-4	Sahafah	6
242	wk-9	Yasmeen	0
243	wk-6	Malqa	8
244	wk-13	Malqa	8
245	wk-11	Sahafah	3
246	wk-13	Yasmeen	6
247	wk-6	Malqa	3
248	wk-11	Yasmeen	0
249	wk-9	Yasmeen	5
250	wk-9	Yasmeen	8
251	wk-14	Yasmeen	8
252	wk-7	Sahafah	0
253	wk-7	Malqa	5
254	wk-6	Sahafah	4
255	wk-7	Yasmeen	6
256	wk-11	Yasmeen	7
257	wk-9	Yasmeen	2
258	wk-13	Yasmeen	6
259	wk-6	Malqa	8
260	wk-4	Sahafah	5
261	wk-6	Yasmeen	2
262	wk-12	Sahafah	2
263	wk-14	Sahafah	4
264	wk-14	Yasmeen	6
265	wk-4	Sahafah	3
266	wk-8	Malqa	5
267	wk-6	Yasmeen	2
268	wk-6	Yasmeen	7
269	wk-4	Malqa	0
270	wk-14	Malqa	1
271	wk-14	Yasmeen	8
272	wk-4	Sahafah	8
273	wk-10	Sahafah	3
274	wk-15	Malqa	2
275	wk-4	Malqa	6
276	wk-4	Malqa	2
277	wk-4	Sahafah	0
278	wk-8	Sahafah	8
279	wk-8	Sahafah	5
280	wk-12	Yasmeen	0
281	wk-13	Sahafah	3
282	wk-9	Malqa	8
283	wk-13	Yasmeen	3
284	wk-5	Yasmeen	5
285	wk-5	Malqa	4
286	wk-7	Sahafah	8
287	wk-13	Malqa	8
288	wk-13	Yasmeen	0
289	wk-7	Malqa	5
290	wk-7	Malqa	4
291	wk-15	Yasmeen	4
292	wk-7	Sahafah	3
293	wk-7	Yasmeen	8
294	wk-9	Yasmeen	5
295	wk-17	Malqa	7
296	wk-9	Yasmeen	0
297	wk-13	Yasmeen	3
298	wk-15	Yasmeen	8
299	wk-5	Malqa	3
300	wk-11	Malqa	3
301	wk-9	Yasmeen	4
302	wk-18	Malqa	8
303	wk-11	Yasmeen	6
304	wk-7	Malqa	7
305	wk-7	Malqa	2
306	wk-9	Malqa	2
307	wk-11	Malqa	8
308	wk-13	Sahafah	3
309	wk-11	Sahafah	6
310	wk-7	Yasmeen	4
311	wk-7	Yasmeen	2
312	wk-9	Sahafah	8
313	wk-7	Sahafah	7
314	wk-13	Sahafah	7
315	wk-16	Sahafah	0
316	wk-9	Malqa	4
317	wk-16	Yasmeen	6
318	wk-18	Sahafah	2
319	wk-18	Sahafah	6
320	wk-16	Yasmeen	2
321	wk-14	Yasmeen	7
322	wk-12	Yasmeen	4
323	wk-7	Sahafah	1
324	wk-17	Malqa	7
325	wk-10	Sahafah	2
326	wk-19	Sahafah	6
327	wk-5	Malqa	3
328	wk-7	Sahafah	6
329	wk-12	Malqa	1
330	wk-14	Malqa	7
331	wk-14	Yasmeen	6
332	wk-10	Yasmeen	6
333	wk-5	Yasmeen	4
334	wk-10	Malqa	5
335	wk-10	Sahafah	2
336	wk-12	Malqa	5
337	wk-19	Yasmeen	0
338	wk-14	Yasmeen	6
339	wk-12	Malqa	2
340	wk-10	Malqa	1
341	wk-20	Yasmeen	6
342	wk-18	Malqa	8
343	wk-20	Malqa	6
344	wk-13	Sahafah	7
345	wk-8	Malqa	5
346	wk-15	Malqa	0
347	wk-13	Yasmeen	6
348	wk-13	Yasmeen	2
349	wk-18	Malqa	3
350	wk-20	Malqa	4
351	wk-20	Malqa	3
352	wk-13	Malqa	1
353	wk-13	Malqa	1
354	wk-13	Malqa	6
355	wk-8	Malqa	2
356	wk-13	Sahafah	2
357	wk-5	Sahafah	2
358	wk-8	Sahafah	8
359	wk-8	Malqa	2
360	wk-10	Malqa	7
361	wk-6	Yasmeen	4
362	wk-8	Sahafah	4
363	wk-21	Sahafah	3
364	wk-8	Yasmeen	1
365	wk-21	Yasmeen	5
366	wk-11	Yasmeen	6
367	wk-6	Malqa	5
368	wk-16	Sahafah	3
369	wk-13	Malqa	0
370	wk-16	Yasmeen	6
371	wk-21	Yasmeen	6
372	wk-21	Sahafah	3
373	wk-6	Yasmeen	5
374	wk-11	Sahafah	2
375	wk-11	Malqa	5
376	wk-16	Sahafah	5
Sheet1

 

[vmjan02]

This is perfect, thanks a lot, I did do this

 

[awoohaw]

This is perfect, thanks a lot, I did do this

Happy to help. Best Wishes.