Named Range question

D

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?
 
W

willwonka

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

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.
 
R

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
 
D

Don

Excellent, exactly what I needed.

Thank you Roger.

Also thank you to others for their ideas.
 
Top