Named Range question

[Don]

I have several named ranges in my spread sheet. Basically I want to vary the
name used in many formulas without having to manually modify those formulas.

For example:

There are many range names defined, say range_one, range_two, etc.

There are many cells that use range names. For example Average(name_one),
sum(name_one), etc.

Again I want to change the range name in these formulas without manually
modifying the formulas.

Any way to do this?

 

[willwonka]

If there are not too many, you can do a Find and Replace and they will
replace the names (even in the formulas).

 

[smw226 via OfficeKB.com]

Hi Don,

Using Find and Replace (Ctr+H) canges all of the instanvces, even in folmulae
but it change your named ragnges for you.

HTH

Simon

 

[Don]

Yes, I know I could do it with find and replace, but because I want to do
this very frequently I was hoping for a more flexible solution.

For example, could all the formulas get the range name from a cell? Then by
changing the range name in that cell all the formulas would pickup the new
name.

 

[willwonka]

Yes, that is possible, take a look the INDIRECT function.

 

[Roger Govier]

Hi Don

In addition to your existing defined named ranges
Define a Name called "rng" as ="range_"&Sheet1!$A$1
Define a Name called "Myrange" as =EVALUATE(rng)

Then change your formula to
=SUM(Myrange)
=AVERGE(Myrange)

Change the value in cell A1 to "one" , "two" etc. as you please

 

[Roger Govier]

=AVERGE(Myrange)

That should of course have read
=AVERAGE(Myrange)

 

[Don]

Excellent, exactly what I needed.

Thank you Roger.

Also thank you to others for their ideas.