use cell reference for named range

[elf21]

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

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

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

I hope I am making myself clear.
Thank you

 

[Dave Peterson]

=count(indirect(a20))

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]

Brilliant!

Thank you!

 

[elf21]

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.

 

[Dave Peterson]

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]

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.