Create dynamic table & column reference without INDIRECT

1
votes

I'm using following dynamic table reference several times in my workbook which is causing performance issues:

SUMIFS(INDIRECT(VLOOKUP($A$1,$A$2:$B$4,2,0)&"[kpi_name]"),INDIRECT(VLOOKUP($A$1,$A$2:$B$4,2,0)&"[filter1]"),UPPER($H13))

based on the input in A1 I need to switch between table1, table2 and table3.

I do this by building a vlookup map in A2:B4 that return the appropiate table names and using this + column name in a indirect formula.

I would like to move to a better solution using INDEX or another named range. I tried to use a named range with a formula returning the right table reference:

=IF(A1=1,table1,IF(A1=2,table2,IF(A1=3,table3,0)))

But this way I can't use named_range[column of table]

Also I can't get a working solution with INDEX...

1
excelexcel-formula
You can't, you are stuck with this model. If it was a row or column reference then we could use INDEX but since it is the parent table, parent sheet is the same, INDIRECT is the only way to go. - Scott Craner
You could fill a fourth table with vba in a worksheet_change event based on the choice in A1, then refer to that table in all your formula. That should speed things up. - Scott Craner
would you add a screenshot - Mo Khalefa
FWIW, I'd use CHOOSE rather than nested IF statements, but you'll still need to specify the column name, or use INDEX/MATCH to find the column using the name. - Rory
Since you're using Tables and structured references, you can use the suggestion made by @Rory like this =SUMIFS(CHOOSE(A1,Table1[kpi_name],Table2[kpi_name],Tabe3[kpi_name]),CHOOSE(A1,Table1[filter1],Table2[filter1],Table3[filter1]),UPPER($H13)). - Domenic

1 Answers

0
votes

Thanks to the comments of the initial question I came up with follow:

SUMIFS(INDEX(CHOOSE(A1,TABLE1,TABLE2),,MATCH(VAR_COLUMN_NAME,INDEX(CHOOSE(A1,TABLE1,TABLE2),1,0),0)),INDEX(CHOOSE(A1,TABLE1,TABLE2),,MATCH("filter1",INDEX(CHOOSE(A1,TABLE1,TABLE2),1,0),0)),UPPER(ABC))