Naming groups of named ranges

[wholesaleguy]

I have an large spreadsheet with 89 different named ranges that I want to use in an index formula with matches. Can I name a group of named ranges so that I don't have to reenter this data multiply times in the formula?

Thanks for your help!

 

[doofusboy]

Have you tried creating a named range for each group that references the named ranges you want grouped together?

 

[wholesaleguy]

No I have not. I am using an Index formula with match arguments and I thought that the reference areas had to be seperated by commas. Maybe I am wrong, but your suggestion would work if they do not.

Formula example: =INDEX((ref area#1, ref area#2, ref area#3)),MATCH($M$2320,SIZE,0),MATCH($O$2320,COLUMNS,0),MATCH($N$2320,DOORT,0))

I would have 89 different "ref areas" to input into this formula. Each one is of these "ref areas" are a named range of cells, and I would love to have an easier way to do this.

Let me know what you think.

 

[doofusboy]

I would have 89 different "ref areas" to input into this formula.

That's a lot of typing and why you might consider a different approach.

In order to enter fewer "ref areas" you asked if you could group some of them and give that group a name. The answer is yes, you can.

Try this simple test to get the concept down and then apply it to your workbook. I named the range A1:A5 as 'MyNames1' and named the range B1:B5 as 'MyNames2'. Then I created a third named range called 'Group1' by entering "=MyNames1,MyNames2" in the Refers To: field of the named range's dialog box.

Then, I put the following code into Worksheet_Change to see if I could refer to 'Group1' in the formula to see if it could detect changes made in either 'MyNames1' or 'MyNames2'. It worked just fine and detected when a change was made in either named range. You should be able to apply the same principle to whatever you are trying to do.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, ThisWorkbook.Names("Group1").RefersToRange) Is Nothing Then
        MsgBox "Change occurred in Group1."
    End If
End Sub

 

[wholesaleguy]

Thanks for your help! This was much easier than I thought it would be.

 

[wholesaleguy]

Is there a limit to the number of characters that can be entered into the "refer to " area of the name manager? I was trying to use the name manager to create the named range as you suggested. After inputting about 20 different named ranges, the software will not accept anymore inputs from the keyboard. It seems that it has a limit to the number of characters you can enter into this field.

 

[doofusboy]

There most likely is some limit. Either on the number of named ranges you can enter as one group, or some sort of character limit; however, I don't know off the top of my head. Would suggest researching directly on Microsoft's web site.

 

[wholesaleguy]

one more idea: if I group my 89 different named ranges into groups of 10 by creating new named ranges, can I use data validation or user input in some way to try and narrow down which group of ten I will then use in a separate index / match formula with data validation? I have tried to experiment by creating a table that contains the different named ranges (groups) and then use the output from this index / match formulas to be the reference in a second index / match formulas with data validation. I just don't know how to get the second formula to see the output of the first as a named range instead of text like "group1".

Any other ideas?

 

[mikerickson]

INDEX doesn't work (well) with discontinous ranges.
Instead of union, perhaps the range operator : would help.

=INDEX((range1:range2:range3:range4), ...)

Which would be the same as if you used only the two extreme named ranges

=INDEX(range1:range4, ...)

 

[wholesaleguy]

doesn't seem to work the way I am using it... let me ask it this way:
using the following formula: INDEX("ALL POSSIBLE REFERENCES SEPERATED BY A COMMA"),MATCH("DATA VALIDATION RESULT1","NAMED RANGE1",0),MATCH("DATA VALIDATION RESULT2","NAMED RANGE2",0),MATCH("NAMED RANGE3",0))
How can I use a data validation cell or some kind of user input to narrow the range (bold / italic) of this formula based on a users choice?