Get Excel to recognize a Name in a formula through a referenced cell with a dropdown

%3CLINGO-SUB%20id%3D%22lingo-sub-2842743%22%20slang%3D%22en-US%22%3EGet%20Excel%20to%20recognize%20a%20Name%20in%20a%20formula%20through%20a%20referenced%20cell%20with%20a%20dropdown%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842743%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3EI%20have%20defined%20a%20few%20different%20named%20ranges%20in%20an%20Excel%20spreadsheet%20and%20am%20trying%20to%20have%20a%20SUMIF%20formula%20where%20I%20can%20use%20a%20dropdown%20to%20change%20the%20name%20that%20the%20cell%20is%20pulling.%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CP%3EEach%20named%20range%20is%20a%20list%20based%20on%20project%20status.%20For%20example%2C%20Not_to_Senior_Yet%20refers%20to%20anything%20that%20is%20%22Not%20Started%22%20or%20%22In%20Progress%22.%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%20class%3D%22%22%3EMy%20formula%20works%20fine%20when%20I%20actually%20type%20out%20the%20name%20of%20the%20range.%20For%20example%3A%3C%2FP%3E%3CP%20class%3D%22%22%3E%3DSUM(SUMIFS(DHM!%24K%3A%24K%2CDHM!%24I%3A%24I%2CNot_to_Senior_Yet))%3C%2FP%3E%3CP%20class%3D%22%22%3EThis%20sums%20all%20of%20the%20items%20from%20the%20DHM%20tab%20that%20are%20either%20Not%20Started%20or%20In%20Progress.%3C%2FP%3E%3CP%20class%3D%22%22%3EHowever%2C%20if%20I%20replace%20Not_to_Senior_Yet%20with%20a%20cell%20reference%20to%20where%20that%20text%20is%20typed%2C%20it%20no%20longer%20recognizes%20the%20name%20and%20the%20formula%20returns%200.%20I%20want%20the%20formula%20to%20work%20such%20that%20I%20can%20select%20Not_to_Senior_Yet%20or%20Not_to_Manager_Yet%20etc.%20and%20the%20formula%20will%20update%20automatically%2C%20rather%20than%20the%20name%20having%20to%20be%20embedded%20in%20the%20formula.%20For%20example%3A%3C%2FP%3E%3CP%20class%3D%22%22%3E%3DSUM(SUMIFS(DHM!%24K%3A%24K%2CDHM!%24I%3A%24I%2C%24F%242))%20where%20%24F%242%20has%20a%20dropdown%20that%20I%20can%20change%20to%20Not_to_Senior_Yet%20or%20Not_to_Manager_Yet.%3C%2FP%3E%3CP%20class%3D%22%22%3EAny%20suggestions%20on%20how%20to%20force%20Excel%20to%20recognize%20the%20Name%20referenced%20in%20%24F%242%3F%3C%2FP%3E%3CP%20class%3D%22%22%3EExcel%20Version%20is%20Microsoft%20365%20Apps%20for%20Business%20(Version%202109)%20on%20a%20Windows%20PC%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2842743%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I have defined a few different named ranges in an Excel spreadsheet and am trying to have a SUMIF formula where I can use a dropdown to change the name that the cell is pulling.

Each named range is a list based on project status. For example, Not_to_Senior_Yet refers to anything that is "Not Started" or "In Progress".

My formula works fine when I actually type out the name of the range. For example:

=SUM(SUMIFS(DHM!$K:$K,DHM!$I:$I,Not_to_Senior_Yet))

This sums all of the items from the DHM tab that are either Not Started or In Progress.

However, if I replace Not_to_Senior_Yet with a cell reference to where that text is typed, it no longer recognizes the name and the formula returns 0. I want the formula to work such that I can select Not_to_Senior_Yet or Not_to_Manager_Yet etc. and the formula will update automatically, rather than the name having to be embedded in the formula. For example:

=SUM(SUMIFS(DHM!$K:$K,DHM!$I:$I,$F$2)) where $F$2 has a dropdown that I can change to Not_to_Senior_Yet or Not_to_Manager_Yet.

Any suggestions on how to force Excel to recognize the Name referenced in $F$2?

Excel Version is Microsoft 365 Apps for Business (Version 2109) on a Windows PC

1 Reply
just add INDIRECT() around that cell reference:
=SUM(SUMIFS(DHM!$K:$K,DHM!$I:$I,INDIRECT($F$2)))