Forum Discussion
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 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.
=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.