Way to reference data range names via a cell?

[TurboJ]

Seems simple but I can't get it to work. I have a variety of similar summary data I am doing across various data sets that needs to remain seperate. The formulars are extremely complex and based on pivot tables in which I have various dynamic name ranges set up in. When I go to recreate the summary for another group of data I have to go into each set of complex calculations and change the name ranges its referencing.

Is there a way to just reference a cell in this case instead of inputting the name of the range 20 X and put the single name range in that cell. That way when I go to duplicate it on other data I can just type the new name range in the cell.

To simplify;

Data Range: Data1
Forumla: Max(Data1)

Later I need to use the same Formula but need it for Data2. Instead of having to go and change Data1 to Data2, can I do something like.

Have Cell C1 = "Data1"
Forumla: Max(C1) or Max(Indirect(C1))

then when I duplicate this for other areas I can just change the value in C1 instead of in my formula?

 

[Jonmo1]

Indirect would be the way to go, but unfortunately it doesn't work with 'dynamic' named ranges.

It would work fine if Data1 was a hard coded named range like =$A$1:$A$10
But if Data1 is dynamic in anyway like =OFFSET(A1,0,0,COUNTA(A:A),1)
Then it won't.

How many named ranges are there that you would possibly change C1 to use?

 

[TurboJ]

The name ranges are dynamic in the fact the name range formula is = Indirect(Y1):Indirect(X1) which reference cells that have cell addresses I derived in them to allow the name ranges to be dynamic since I'm working in a pivot table environment so the data range is constently changing.

Since I am using it like that you are saying I can't us it in the manner I want correct? I have 8=10 different name ranges that I would use it for. Each master set of data/calcs has 20-40 references of these name ranges through out the calcs so it would be more effecient just to be able to set it up referencing one master "name range" cell description for that set of calcs. Oh well not a huge deal but I had other applications I would love to use it for. Just getting into trying to automate alot of data so kind of learning as I go.

 

[Jonmo1]

Since I am using it like that you are saying I can't us it in the manner I want correct?

That's correct, unfortunately.

If it's only 8 - 10 ranges, you can do it this way.

Make a list of those names in another range, say G1:G10 or whatever.
Then use
=MAX(CHOOSE(MATCH(C1,$G$1:$G$10,0),Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Data9,Data10))

Actually, that would work for up to 254 ranges, but it gets rediculouse at some point...lol