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:


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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.