Changing a named range within a formula

[Mark b.]

I'm trying to set up a macro that will update the reference to a anamed range
within a formula.

For example, cell A1 contains a value of "LastName" which also happens to be
a named range.

Cell B1 contains the formula "=Count(LastName)"

When I change the value of A1 to the name of another named range, i.e.
"Address", and run the code, I want the formula in B1 to change to
"=Count(Address)"

Ideas?

 

[Chip Pearson]

Create a name called, for example, CountRange and assign it to
=COUNT(INDIRECT($A$1))

Then call it from a cell with

=CountRange

It will return the COUNT of the range whose name appears in cell A1.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)