Reputation: 411
I am attempting to alter a spreadsheet that currently uses INDIRECT to dynamically determine a column range in a table. It's really slow.
I have a vba function that will determine the column name, but I now need to reference the column identified by that name. The end goal is to use it in a SUMIF statement.
Currently uses: =SUMIFS(My_Table_Name[UNIT_TOTAL],INDIRECT(MyNamedItem),1)
The value of MyNamedItem is My_Table_Name[THE_COLUMN]
I can return My_Table_Name[THE_COLUMN]
as a string by a function, but then can't pass that into the SUMIF
as an argument. How do I return it as a table column range which can be passed as an argument?
The formula could then be: =SUMIFS(My_table_name[UNIT_TOTAL],My_function_Name(),1)
Upvotes: 0
Views: 291
Reputation: 35990
Consider this screenshot:
The formula in cell K2 is
=SUMIFS(Table1[Unit Total],INDEX(Table1,,MATCH(I2,Table1[#Headers],0)),J2)
copied down. The table column is calculated dynamically with an Index/Match combo.
Upvotes: 1