6

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?

1
  • Will this help you?
    – Voitcus
    Aug 25, 2013 at 14:06

2 Answers 2

6

Wrap it all in an INDIRECT function. INDIRECT translates a string into a reference.

=COUNTIF(INDIRECT("RANGE" & "whatever comes after"),1)
2

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)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .