CODEWITHSUNDEEP
excelvba
user9588528
user9588528

Reputation: 411

Referencing a table range, based on a column name

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

Answers (1)

teylyn
teylyn

Reputation: 35990

Consider this screenshot:

enter image description here

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

Related Questions