Forum Discussion

michael guffey's avatar
michael guffey
Copper Contributor
Jun 17, 2018

Using a specific cell to reference table name in a minifs or maxifs funtion

I have multiple minif and maxif funtions, I use the name of table headers for my min/max range, id like to simply use a cell to reference the name of the header rather than manually having to change the name.

 

for instance I have:

 

=MINIFS(table23[Grades],criteria range1,criteria1...….

 

I want to do this:

 

=MINIFS(Table23[D1],criteria range1,criteria1...….

with cell D1=Grades

 

 and simply have the name of the table header I want the minif to return in cell D1. However it returns an error.

 

Thank you for any help!

 

Formulas & Functions
Need Help

1 Reply

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jun 18, 2018
    The function you are looking for is the INDIRECT function:
    =MINIFS(INDIRECT("Table23["&D1&"]"),....
    HOwever, I find it a bad idea to actually use INDIRECT because:

    1. The function is volatile, causing your (slow!) MINIF function to reclacluate every time Excel calculates, even if nothing has changed
    2. INDIRECT is notoriously hard to trouble-shoot and audit
    3. You loose the fact that changing the name of the table or the name of the column header automatically updates your formulas to reflect that change.

Resources