use cell reference for named range

I have named several ranges on my sheet with names of cities such as "newYork", "Chicago", "sanDiego" etc.

I would like to use these ranges in a formula, but rather than type in these ranges I would like to use a cell reference that contains these names.

For example, instead of typing =COUNT(Chicago) where "Chicago" is a range I have defined, I would like to be able to type something like =COUNT(A20) where A20 contains the text Chicago.

I hope I am making myself clear.
Thank you!

=count(indirect(a20))

elf21 wrote:
>
> I have named several ranges on my sheet with names of cities such as
> "newYork", "Chicago", "sanDiego" etc.
>
> I would like to use these ranges in a formula, but rather than type in
> these ranges I would like to use a cell reference that contains these
> names.
>
> For example, instead of typing =COUNT(Chicago) where "Chicago" is a
> range I have defined, I would like to be able to type something like
> =COUNT(A20) where A20 contains the text Chicago.
>
> I hope I am making myself clear.
> Thank you!
>
> --
> elf21
> ------------------------------------------------------------------------
> elf21's Profile: http://www.excelforum.com/member.php...o&userid=31660
> View this thread: http://www.excelforum.com/showthread...hreadid=513468

--

Dave Peterson

Brilliant!

Thank you!

But you can't have a named range that includes a space.

If you name the range San_Diego (note the underscore), you could use:
=count(indirect(substitute(a20," ","_")))

If you name the range SanDiego (no spaces), you could use:
=count(indirect(substitute(a20," ","")))

elf21 wrote:
>
> I just realized it doesn't work if the cell contains spaces such as in
> "San Diego". I suppose it only reads the first word: San. How can I
> get it to interpret the entire cell??
>
> Thanks.
>
> --
> elf21
> ------------------------------------------------------------------------
> elf21's Profile: http://www.excelforum.com/member.php...o&userid=31660
> View this thread: http://www.excelforum.com/showthread...hreadid=513468

--

Dave Peterson

I realized that shortly after I wrote it, that's why I deleted my post. But I see you got to it before I had a chance to delete it!

Thank you for that other tip - using substitute. It allows for me to retain the spaces within the contents of the cells - which simply looks better on the spreadsheet.