Excel reference named range with text and formula
Is it possible to refer to a named range by combining text with a formula? Say I get a name for a range via INDEX which returns a text but I also want to add something after the text like so: RANGE & "Whatever comes after" I have several named ranges so in order to dynamically refer to those ranges I need to add something after that RANGE text. Excel however gives an error. In a nutshell Named ranges: RANGE1 RANGE2 RANGE3 etc. What I want is say with a count formula: =COUNTIF(RANGE & "whatever comes after",1) If I try to do this then Excel gives an error so is what I'm trying to do even possible?
Wrap it all in an
INDIRECT function. INDIRECT translates a string into a reference.
=COUNTIF(INDIRECT("RANGE" & "whatever comes after"),1)
As Voitcus indicated, try the INDIRECT command to build the query string. The range part should look something line:
indirect(RANGE & "whatever comes after")
I’m not sure what you’re trying to do with the “,1” in your COUNTIF statement but try:
=countif(indirect(range & "whatever comes after"),1)
Our Awesome Tools
- Check your IP Address precisely
- Online JSON Formatter with Syntax Highlight
- Online CSS Minifier Compressor