Creating an Excel Rolling Chart

[TomasTeix]

I´m having trouble creating a rolling chart regarding the syntax of the OFFSET Function for specifying a dynamic range. i want to make the table so that. when a new column header is added, the same values of previous collumns are added bellow this new collumn header aswell. I´m doing this by `Defining Names´ and then adding a `New Name´ . In the `Refers To´ section I´m writing the following (I have 2 sheets, and 2 Tables on the sheet I´m using):

=OFFSET(TableName[[#Headers];[Value in Header]],COUNT(TableName[#Headers])0,-20,1)

I want o select the last 20 columns. Im getting a syntax error message. Why is this not working?

 

[GraH]

Since you have 365, maybe try with TAKE(TableName[[#Headers],,-20). That will dynamically return the last 20 columns.

With offset, you can try this =OFFSET(XLOOKUP("zzzz",TableName[#Headers];Table1[#Headers],,-1;2),,,,-20)

 

[TomasTeix]

This unfortunaley also gives me a syntax mistake

 

[GraH]

Can you share a sample data?

I noticed, I forgot to update the second Table Reference, maybe that was the cause.
=OFFSET(XLOOKUP("zzzz",TableName[#Headers];TableName[#Headers],,-1;2),,,,-20)

This dummy chat is created using the take function.

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
2	Any Col Title	Any Col Title2	Any Col Title3	Any Col Title4	Any Col Title5	Any Col Title6	Any Col Title7	Any Col Title8	Any Col Title9	Any Col Title10	Any Col Title11	Any Col Title12	Any Col Title13	Any Col Title14	Any Col Title15	Any Col Title16	Any Col Title17	Any Col Title18	Any Col Title19	Any Col Title20	Any Col Title21	Any Col Title22	Prev end	Cur End
3	23	9	7	15	12	8	17	9	25	6	15	12	5	25	17	9	19	8	21	8	16	14	11	13
4	6	10	9	18	9	24	21	14	5	18	8	25	10	23	11	15	24	17	6	21	18	17	10	11
5	8	18	11	9	10	8	25	20	16	8	12	21	24	7	14	17	21	22	13	13	6	25	24	22
6	20	23	15	10	25	12	25	6	24	7	17	17	6	22	12	10	25	9	25	15	15	9	19	14
7
8
9
10
11	Any Col Title5	Any Col Title6	Any Col Title7	Any Col Title8	Any Col Title9	Any Col Title10	Any Col Title11	Any Col Title12	Any Col Title13	Any Col Title14	Any Col Title15	Any Col Title16	Any Col Title17	Any Col Title18	Any Col Title19	Any Col Title20	Any Col Title21	Any Col Title22	Prev end	Cur End
12	12	8	17	9	25	6	15	12	5	25	17	9	19	8	21	8	16	14	11	13
13	9	24	21	14	5	18	8	25	10	23	11	15	24	17	6	21	18	17	10	11
14	10	8	25	20	16	8	12	21	24	7	14	17	21	22	13	13	6	25	24	22
15	25	12	25	6	24	7	17	17	6	22	12	10	25	9	25	15	15	9	19	14
Sheet1

Cell Formulas
Range	Formula
D3:AA6	D3	=RANDBETWEEN(5,25)
D11:W15	D11	=VSTACK(TAKE(Table1[#Headers],,-20),TAKE(Table1,,-20))
Dynamic array formulas.