Dynamic Graphs/Offset

[chef]

Hi
Using office 365 and I am having problems with dynamic graphs and using offset formula

I have a table of individual headings in B5 to B14 in a workbook called Family
Months are C4 to N4 ( Apr-21 to March 22 )

When I map out a graph for each heading I have to amend 10 graphs manually each month as the graph drops to 0 after current month and its time consuming and I am also only showing the currents mths value as a label
So for example I need to graph heading Admin Cell B6 and pick up values C6 to N6 and show months C4 to N4 on graph and then for all other headings but stop all graphs dropping to 0 and automatically changing when next months values input
Hope this makes sense

I have spent hours with named ranges and offset/counta etc but to be honest i am not really sure how it all gets glued together , cant get to work and would really appreciate any help at all

I have another horizontal one on another sheet called Workforce with months C13 to C14 and values E13 to E14 and assuming same concept but i cant get my head around the offset/counta/columns

Im hoping there is a solution and a quicker way to do this.


regards
ian

 

[RasGhul]

Hi Chef,

There's probably an easier way and maybe this suits, but below I'm using Filter & index to extract/return only dates between start & current month. There's a drop-down to dynamically swap between headings with one chart instead of 10.

When inserting your chart make sure to highlight the total range of what you data can return eg B12:N22 (april - Mar) is the total area the filter and index would use.

Family.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
4	Months	1/04/2021	May-2021	Jun-2021	Jul-2021	Aug-2021	Sep-2021	Oct-2021	Nov-2021	Dec-2021	Jan-2022	Feb-2022	Mar-2022
5	Headings1	1	2	3	4	5	6	7	8	9	10	11	12
6	Admin	165	166	167	168	169	170	171	172	173	174	175	176
7	Headings3	1	2	3	4	5	6	7	8	9	10	11	12
8	Headings4	22	23	24	25	26	27	28	29	30	31	32	33
9	Headings5	1	2	3	4	5	6	7	8	9	10	11	12
10	Headings6	1	2	3	4	5	6	7	8	9	10	11	12
11	Headings7	1	2	3	4	5	6	7	8	9	10	11	12
12	Headings8	1	2	3	4	5	6	7	8	9	10	11	12
13	Headings9	1	2	3	4	5	6	7	8	9	10	11	12
14	Headings10	1	2	3	4	5	6	7	8	9	10	11	12
15
16
17	Start Month	1/04/2021
18	Current EOM	30/11/2021
19	Heading	Admin
20
21		Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21
22	Admin	165	166	167	168	169	170	171	172
Sheet1

Cell Formulas
Range	Formula
D4:N4	D4	=EOMONTH(C4,0)+1
C17	C17	=C4
C18	C18	=EOMONTH(TODAY(),0)
C21:J21	C21	=FILTER(Months,($C$4:$N$4>=$C$17)*($C$4:$N$4<=$C$18))
B22	B22	=C19
C22:N22	C22	=IFNA(INDEX(Data,MATCH($B$22,Headings,0),MATCH(C$21,Months,0)),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Data	=OFFSET(Sheet1!$C$5,,,COUNT(Sheet1!$C$5:$C$14),COUNT(Sheet1!$C$5:$N$5))	C22:N22
Headings	=Sheet1!$B$5:$B$14	C22:N22
Months	=OFFSET(Sheet1!$C$4,,,COUNTA(1),COUNT(Sheet1!$C$4:$N$4))	C22:N22, C21, D4, C17

Cells with Data Validation
Cell	Allow	Criteria
C19	List	=Headings

 

[chef]

I will give this a go and map our graphs from this

Many many thanks for taking the time to help me. It's much appreciated
regards

 

[RasGhul]

You're welcome