I have 60 named ranges that I want to reference in a formula that I can copy and paste, so that I have one formula instead of 60 different formulas. I was thinking I'd have a column that says the name of the named range to reference, but in an averageif formula, it doesnt recognize the name written in the cell as a named range. Is there a way I can make the formula recognize that I'm referring to a named range title in a cell?
The formula im using now is like this...... =AVERAGEIFS(<NAMED RANGE I WANT TO REFERENCE>, Specialist, $F$1, Region, G$2)*100 .
Referencing a named range in an excel formula
-
schneid8
- NewLounger
- Posts: 1
- Joined: 09 Apr 2021, 04:34
-
HansV
- Administrator
- Posts: 78402
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Referencing a named range in an excel formula
Welcome to Eileen's Lounge!
You can use the INDIRECT function for this purpose.
Let's say you have the name of the range in cell D2:
=AVERAGEIFS(INDIRECT(D2), Specialist, $F$1, Region, G$2)*100
You can use the INDIRECT function for this purpose.
Let's say you have the name of the range in cell D2:
=AVERAGEIFS(INDIRECT(D2), Specialist, $F$1, Region, G$2)*100
Best wishes,
Hans
Hans