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!
.... for the last x number of items in my row.
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 |
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
=OFFSET(Sheet17!$D$4,0,LOOKUP(2,1/(Sheet17!$D$7:$AAA$7<>""),COLUMN(Sheet17!$D$7:$AAA$7))-35,1,36)
A sample file with a solution was provided at the cross-posted siteCross posted at: Dynamic Named Range - last x number of items
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 .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.