Named Ranges workaround?
- Thread starter karl01
- Start date
NARAYANK991
Excel Ninja
Hi ,
What would be the application of this concept ?
A named range designates an area of a worksheet ; by using the same name to designate several different areas , how would Excel be able to understand which area is being referred to ?
Narayan
What would be the application of this concept ?
A named range designates an area of a worksheet ; by using the same name to designate several different areas , how would Excel be able to understand which area is being referred to ?
Narayan
karl01
New Member
Hi Narayan,
Thanks for your response
Im wondering if there is a workaround in which i can use same names for different data set but still keep it independent of main list.
Let me try explain... If you look at example file attached (sheet 2) if i was to place data in cells c4:c5:6 that is same data as b4:b5:b6 then attempt to change the name ranges to match in i2:j2:k2 obviously its not possible it will change named range values to 12:22:32 instead of 1,2,3 respectively.
i dont want to have letters after the numbers in the ranges. But the name range needs to be named the same for each quality.
would it work if i made a separate sheet for each named range and link to master list, would this be a suitable workaround?
Thanks for your response
Im wondering if there is a workaround in which i can use same names for different data set but still keep it independent of main list.
Let me try explain... If you look at example file attached (sheet 2) if i was to place data in cells c4:c5:6 that is same data as b4:b5:b6 then attempt to change the name ranges to match in i2:j2:k2 obviously its not possible it will change named range values to 12:22:32 instead of 1,2,3 respectively.
i dont want to have letters after the numbers in the ranges. But the name range needs to be named the same for each quality.
would it work if i made a separate sheet for each named range and link to master list, would this be a suitable workaround?
NARAYANK991
Excel Ninja
Hi ,
I am still somewhat unsure about what exactly you want to do.
1. You have a main list in the table column named Quality.
2. The choices in this main list now need to form the header labels in the subsequent columns B , C , D , E ,...
3. The above scheme will need to be followed for all the sub-lists i.e. the items in any one sub-list cannot match items in any other sub-list , since otherwise the DV dropdown will display the wrong items for selection.
I am not sure how shifting the named ranges to separate worksheets will help. Suppose we consider the items under the header hypertrophy are :
Item1 , Item2 , Item3 ,...
Suppose the items under the header relativestrength are identical.
Obviously , the table Table2 will have headers labelled Item1 , Item2 , Item3 ,...
In Sheet1 , if in the column B , in B5 , you select hypertrophy and in B6 you select relativestrength ; the cells C5:F5 as well as the cells C6:F6 will all display the same choices in their dropdowns viz. Item1 , Item2 , Item3 ,...
Now , its a matter whether you want the same DV dropdown to be displayed if say Item1 is selected in row 5 or Item1 is selected in row 6.
If all the dropdowns are in the same table Table2 , then only this will be possible.
If all the dropdowns are in different worksheets , then depending on whether hypertrophy is selected and Item1 is selected or relativestrength is selected and Item1 is selected , the DV dropdown for the two choices can be different.
The point is that the scheme that has been chosen requires the table headers to be unique ; if a different scheme is chosen for implementing dependent DV dropdowns , then what you want done may be possible.
I would suggest that you ignore what you have already implemented ; describe your complete application , and upload a file which has all the input data including the dropdowns. What is required is a detailed description of :
1. What should the dropdown in Sheet1 column B present to the user ?
2. What should the dropdowns in Sheet1 columns C through F present to the user ?
Narayan
I am still somewhat unsure about what exactly you want to do.
1. You have a main list in the table column named Quality.
2. The choices in this main list now need to form the header labels in the subsequent columns B , C , D , E ,...
3. The above scheme will need to be followed for all the sub-lists i.e. the items in any one sub-list cannot match items in any other sub-list , since otherwise the DV dropdown will display the wrong items for selection.
I am not sure how shifting the named ranges to separate worksheets will help. Suppose we consider the items under the header hypertrophy are :
Item1 , Item2 , Item3 ,...
Suppose the items under the header relativestrength are identical.
Obviously , the table Table2 will have headers labelled Item1 , Item2 , Item3 ,...
In Sheet1 , if in the column B , in B5 , you select hypertrophy and in B6 you select relativestrength ; the cells C5:F5 as well as the cells C6:F6 will all display the same choices in their dropdowns viz. Item1 , Item2 , Item3 ,...
Now , its a matter whether you want the same DV dropdown to be displayed if say Item1 is selected in row 5 or Item1 is selected in row 6.
If all the dropdowns are in the same table Table2 , then only this will be possible.
If all the dropdowns are in different worksheets , then depending on whether hypertrophy is selected and Item1 is selected or relativestrength is selected and Item1 is selected , the DV dropdown for the two choices can be different.
The point is that the scheme that has been chosen requires the table headers to be unique ; if a different scheme is chosen for implementing dependent DV dropdowns , then what you want done may be possible.
I would suggest that you ignore what you have already implemented ; describe your complete application , and upload a file which has all the input data including the dropdowns. What is required is a detailed description of :
1. What should the dropdown in Sheet1 column B present to the user ?
2. What should the dropdowns in Sheet1 columns C through F present to the user ?
Narayan
karl01
New Member
Hi Narayan,
Thanks again for your time.
I think this answers the question:
"i.e. the items in any one sub-list cannot match items in any other sub-list"
"The point is that the scheme that has been chosen requires the table headers to be unique"
This is the problem because the named ranges and table headers have some duplicate content for each quality. So i can display what i need as per example only if i precede the named range and qualities table with a letter or something to differentiate it.
So i could have hypertrophy named ranges and 1,2,3 but i couldn't then have relative strength named ranges as 1,2,3 aswell with out adding something to change the name.
The issue i had with this was i didn't want the drop down data to be shown with a number and then a letter to set the duplicate content apart. I just want lists of numbers but because of the duplicate data i cant do this due to the named ranges needing to be different.
il have a go at it again later and see what i can figure out (im new to all this so slowly learning - slowly being the keyword!
Thanks again
Thanks again for your time.
I think this answers the question:
"i.e. the items in any one sub-list cannot match items in any other sub-list"
"The point is that the scheme that has been chosen requires the table headers to be unique"
This is the problem because the named ranges and table headers have some duplicate content for each quality. So i can display what i need as per example only if i precede the named range and qualities table with a letter or something to differentiate it.
So i could have hypertrophy named ranges and 1,2,3 but i couldn't then have relative strength named ranges as 1,2,3 aswell with out adding something to change the name.
The issue i had with this was i didn't want the drop down data to be shown with a number and then a letter to set the duplicate content apart. I just want lists of numbers but because of the duplicate data i cant do this due to the named ranges needing to be different.
il have a go at it again later and see what i can figure out (im new to all this so slowly learning - slowly being the keyword!
Thanks again
jeffreyweir
Active Member
Assuming your requirement is to set up cascading dropdowns with non unique subcategories, I know a clever - but complicated - way around this that I've used to answer a similar question on this forum before at http://forum.chandoo.org/threads/dynamic-drop-downs-with-large-table.18496/ It involves turning your dropdown categories into a series of PivotTables, and then using something called Range Slicing. Works great, but is very complicated to set up. I've been meaning to code up an add-in that sets this up, and so this presents a good opportunity.
Can you supply your lookup criteria as a hierarchy?
Can you supply your lookup criteria as a hierarchy?
NARAYANK991
Excel Ninja
Hi Jeff ,
Surely these two situations are different :
1. Where the headers have duplicates
2. Where the options have duplicates
The second situation is what the OP would like to implement , but this does not mean that the first situation has to exist ; can we not have unique headers which have duplicate options ?
The headers are set up once , whereas the options can change from time to time.
Narayan
Surely these two situations are different :
1. Where the headers have duplicates
2. Where the options have duplicates
The second situation is what the OP would like to implement , but this does not mean that the first situation has to exist ; can we not have unique headers which have duplicate options ?
The headers are set up once , whereas the options can change from time to time.
Narayan
jeffreyweir
Active Member
I'll try to find time today to whip up an example and post back. Busy day for me today though...
jeffreyweir
Active Member
Narayan...bit of a confusing thread this one. Yes, you're correct: for a two-level cascading dropdown setup, if you had say 'Meat' and 'Wine' as the only choices in the first level, and then had sublists of 'Red' and 'White' for both of them, then you can quite happily have duplicates between the lists of those 2nd categories. It looks like that is the case here, and the approach that Karl01 is using (which looks like it came from my post at http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/ ) will work just fine as is. Karl01: if that's the case, you don't *need* to have "the same 'Named' Range over multiple ranges within the same worksheet" and so this question is redundant.
However, if you wanted to have a three-level cascading dropdown (or more) , then you can't have any duplicates across those lists (with the exception of the last level).
However, I have another approach that can handle any number of levels with any number of duplicate options between any levels. It uses a series of intermediate tables that I create programmatically from PivotTables.
I first create a master hierarchy like this:
...then I'll create some intermediary tables (using a PivotTable that I then turn to a Table so I have built in named ranges) like these:
...and then I use some pretty complicated range-slicing formulas to serve up the required validation list, with the end result being something like this:
As you can see from the above, if you choose "A" for the first level, "B" for the second, then "B" again for the third, the validation correctly works out and displays just the relevant choices for that combination: ABB1, ABB2, ABB3, and ABB4.
This is horrendously complicated to set up, and requires a lot of fancy Names behind the scenes. But I'm writing an add-in to do it all at the push of a button. I'll upload a file shortly that shows the above...just having problems uploading at present.
However, if you wanted to have a three-level cascading dropdown (or more) , then you can't have any duplicates across those lists (with the exception of the last level).
However, I have another approach that can handle any number of levels with any number of duplicate options between any levels. It uses a series of intermediate tables that I create programmatically from PivotTables.
I first create a master hierarchy like this:
...then I'll create some intermediary tables (using a PivotTable that I then turn to a Table so I have built in named ranges) like these:
...and then I use some pretty complicated range-slicing formulas to serve up the required validation list, with the end result being something like this:
As you can see from the above, if you choose "A" for the first level, "B" for the second, then "B" again for the third, the validation correctly works out and displays just the relevant choices for that combination: ABB1, ABB2, ABB3, and ABB4.
This is horrendously complicated to set up, and requires a lot of fancy Names behind the scenes. But I'm writing an add-in to do it all at the push of a button. I'll upload a file shortly that shows the above...just having problems uploading at present.
Last edited:
jeffreyweir
Active Member
To give you an idea of just how complicated this is, here's all the names it uses for a 4-level dropdown setup. The names Level_2, Level_3, and Level_4 are the 'final' names used by the validation dropdowns, and everything else is workings for them.
jeffreyweir
Active Member
Here's a much simpler - but much more inefficient - implementation. It still uses the same data layout, but uses only a handlful of Names to accomplish the dirty work. The first three are relative names i.e. Left_3 means "Three cells to the left of here".
Left_1 ='DV Tables'!D17
Left_2 ='DV Tables'!C18
Left_3 ='DV Tables'!B19
...and the next three use concatenation on both the lookup terms and the lookup arrays to create 'appropriate' lookup terms and lookup arrays given the position of the current dropdown (e.g. are we dealing with the first dropdown, or the last one, or something in between):
Level_2_List =INDEX(_2[Level 2], MATCH(Left_1,_2[Level 1],0)) : INDEX(_2[Level 2], MATCH(Left_1,_2[Level 1],1))
Level_3_List =INDEX(_3[Level 3], MATCH(Left_2 & Left_1,_3[Level 1] & _3[Level 2],0)) : INDEX(_3[Level 3], MATCH(Left_2 & Left_1,_3[Level 1] & _3[Level 2],1))
Level_4_List =INDEX(_4[Level 4], MATCH(Left_3 & Left_2 & Left_1,_4[Level 1] & _4[Level 2] & _4[Level 3],0)) : INDEX(_4[Level 4], MATCH(Left_3 & Left_2 & Left_1,_4[Level 1] & _4[Level 2] & _4[Level 3],1))
It's that concatenation that makes it inefficient. As per https://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx
In large worksheets, you may frequently need to look up using multiple indexes, such as looking up product volumes in a country. To do this, you can concatenate the indexes and perform the lookup by using concatenated lookup values. However, this is inefficient for two reasons:
It is often more efficient to calculate a subset range for the lookup (for example, by finding the first and last row for the country, and then looking up the product within that range).
...and that is exactly what my much more complicated example does...which is called 'Range Slicing'.
Left_1 ='DV Tables'!D17
Left_2 ='DV Tables'!C18
Left_3 ='DV Tables'!B19
...and the next three use concatenation on both the lookup terms and the lookup arrays to create 'appropriate' lookup terms and lookup arrays given the position of the current dropdown (e.g. are we dealing with the first dropdown, or the last one, or something in between):
Level_2_List =INDEX(_2[Level 2], MATCH(Left_1,_2[Level 1],0)) : INDEX(_2[Level 2], MATCH(Left_1,_2[Level 1],1))
Level_3_List =INDEX(_3[Level 3], MATCH(Left_2 & Left_1,_3[Level 1] & _3[Level 2],0)) : INDEX(_3[Level 3], MATCH(Left_2 & Left_1,_3[Level 1] & _3[Level 2],1))
Level_4_List =INDEX(_4[Level 4], MATCH(Left_3 & Left_2 & Left_1,_4[Level 1] & _4[Level 2] & _4[Level 3],0)) : INDEX(_4[Level 4], MATCH(Left_3 & Left_2 & Left_1,_4[Level 1] & _4[Level 2] & _4[Level 3],1))
It's that concatenation that makes it inefficient. As per https://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx
In large worksheets, you may frequently need to look up using multiple indexes, such as looking up product volumes in a country. To do this, you can concatenate the indexes and perform the lookup by using concatenated lookup values. However, this is inefficient for two reasons:
- Concatenating strings is a calculation-intensive operation.
- The lookup will cover a large range.
It is often more efficient to calculate a subset range for the lookup (for example, by finding the first and last row for the country, and then looking up the product within that range).
...and that is exactly what my much more complicated example does...which is called 'Range Slicing'.
Last edited: