SUMIFS and dynamic named ranges

[kobe26]

1. i have a spreadsheet with data that changes in size so have created dynamic named ranges and the columns names also do change sometimes. So i have a formula that generate the name of columns based of a drop-down list. Would be easy if INDIRECT works with dynamic named ranges but it doesn't so i use a UDF(Getadd(D1) gets me the full address of dynamic named range (LayerData!$BK$2:$BK$100)

2. Inc_Excl Cat_Modelling_Inc_Excl YOA Exposure SI_Current Exposure SI_Proposed 1​ 1​ 2015​ 134,011 134,011 1​ 1​ 2015​ 612,376 612,376 1​ 1​ 2015​ 196,113 196,113 1​ 1​ 2016​ 196,113 196,113 1​ 1​ 2014​ 12,763 12,763 1​ 1​ 2013​ 255,260 255,260 1​ 1​ 2015​ 191,445 191,445 1​ 1​ 2016​ 102,104 102,104 1​ 1​ 2013​ 38,289 38,289

Function Getadd(rangeName As String) As Variant
    Dim rng As Range
   
    On Error Resume Next
    Set rng = Evaluate(rangeName)
    On Error GoTo 0
   
    If Not rng Is Nothing Then
        Getadd = "LayerData!" & rng.Address
       
    Else
        Getadd = "Named range not found or not dynamic."
    End If
End Function

to get the sheet name and address of dynamic name, but when i use it in my SUMIFS, it returns a #VALUE because of double quotation marks (well i guess).

i tried to use SUBSTITUTE to get rid of the quotes but no dice. Been stuck on this for a day now so any help will be greatly appreciated

 

[awoohaw]

Can you just use Tables? Columns in Tables resize automatically. What is an example of a changing column name in your lookup data? And how does it change?
What does your data look like (I am assuming your first table is some kind of calculated report).

Do you have a RAW data range/source or are you processing inside your RAW data?

 

[kobe26]

Can you just use Tables? Columns in Tables resize automatically. What is an example of a changing column name in your lookup data? And how does it change?
What does your data look like (I am assuming your first table is some kind of calculated report).

Do you have a RAW data range/source or are you processing inside your RAW data?

For some reason senior stakeholders prefer it not to be a table(they do use the raw data at times). Example of the columns changing is say Exposure_SI_Current changing to Exposure_SI_Inforce(there are about 200 columns and about 40 can change. My table are all raw data, the calculations are all done in python. I use vba to create all the dynamic named ranges. Hope this clarifies it a bit

 

[awoohaw]

By::
Exposure_SI_Current changing to Exposure_SI_Inforce do you mean they rename the column or are they actually different values?