Dynamic Named Range - last x number of items

[jbusby825]

I am trying to create a dynamic named range for the last x number of items in my row. I have data in row 9 columns D thru AZ, but I want my named range to be the last 36 columns in that row so i want it to be columns Q thru AZ. Thank you in advance!

 

[StephenCrump]

.... for the last x number of items in my row.

Can you please provide more detail? How are you defining the dynamic range? Do you always want the last 36 columns? Or does it depend on the cell contents in the row?

Please also update your Account Details to let us know what version of Excel you're using. With 365, it could be as simple as TAKE(SomeRange,,-36) but I suspect you're using an older version?

 

[jbusby825]

Attached is an image of the part of the file I'm currently referencing. as mentioned i'm looking at row 9 for now, but this will eventually carry over into other rows for other named ranges all to be used in charts. my charts will be for 36 month trends so i want to be able to have the dynamic range looking at the last 36 months.

 

[StephenCrump]

Perhaps something along these lines?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
2	100.0	105.7	108.6	109.6	114.7	118.4	117.7	120.6	119.9	119.8	121.7	122.1	128.0
3
4		EndDate	Jan-24
5		Period (N)	6	months
6
7		ChartData	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24
8			120.6	119.9	119.8	121.7	122.1	128
Sheet1

Cell Formulas
Range	Formula
A1:X1	A1	=EDATE(DATE(2023,1,1),SEQUENCE(,24,0))
C7:H8	C7	=CHOOSECOLS(Data,SEQUENCE(N,,MATCH(C4,A1#)-N+1))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Data	=Sheet1!$A$1:$X$2	C7
N	=Sheet1!$C$5	C7

(I haven't applied any checks that your EndDate is not too old/recent)

If you like, you can chart the results without displaying them, by setting :

chart x values: =Sheet1!x
chart y values: =Sheet1!y

where:

x: =CHOOSECOLS(INDEX(Data,1,),SEQUENCE(N,,MATCH($C$4,$A$1#)-N+1))
y: =CHOOSECOLS(INDEX(Data,2,),SEQUENCE(N,,MATCH($C$4,$A$1#)-N+1))

 

[jolivanes]

To just create a Named Range.

Sub One_Possibility()
Dim fc As Long
fc = Cells(9, Columns.Count).End(xlToLeft).Column - 35
ActiveWorkbook.Names.Add Name:=ActiveSheet.Name & "!RowNine", RefersTo:=ActiveSheet.Range(Cells(9, fc), Cells(9, fc + 36))
Sheets("Sheet1").Range("RowNine").Select
End Sub

 

[Eric W]

You can enter this function directly in the Name Manager:

=OFFSET(Sheet17!$D$4,0,LOOKUP(2,1/(Sheet17!$D$7:$AAA$7<>""),COLUMN(Sheet17!$D$7:$AAA$7))-35,1,36)

Give it a name you like. This looks for the last used column in row 7, so make sure there's nothing after it.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Dynamic Named Range - last x number of items
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[6StringJazzer]

Cross posted at: Dynamic Named Range - last x number of items

A sample file with a solution was provided at the cross-posted site

 

[jbusby825]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Dynamic Named Range - last x number of items
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I got called out on the other post. I didn't know that was a thing. I do not do this too often so I apologize. I know for the future though .