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

--
elf2
-----------------------------------------------------------------------
elf21's Profile: http://www.excelforum.com/member.php...fo&userid=3166
View this thread: http://www.excelforum.com/showthread.php?threadid=51346

=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!


--
elf21
------------------------------------------------------------------------
elf21's Profile: http://www.excelforum.com/member.php...o&userid=31660
View this thread: http://www.excelforum.com/showthread...hreadid=513468

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

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.


--
elf21
------------------------------------------------------------------------
elf21's Profile: http://www.excelforum.com/member.php...o&userid=31660
View this thread: http://www.excelforum.com/showthread...hreadid=513468