• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Best Setup for Massive Dynamic Drop Down

M

MITchip

New Member
I have a large data set (that could change over time) for which I want to create dynamic drop downs. The attached file has a snippet of similar data (A-D). My issue is that it will take 750 named ranges to cover all the possible combinations of data. I certainly don't want to do them all manually, and with the likelihood that the data set will change over time I think my best course of action is to write a macro that will create and name all of the possible named ranges.

I just wanted to see if anybody had any better ideas before I start writing the macro.
 

Attachments

  • BigDynDropSetup.xlsx
    47.7 KB · Views: 15
MITchip

You may also want to contact member JeffreyWeir by a Conversation
http://forum.chandoo.org/members/jeffreyweir.5316/
He is the resident Drop Down guru

He is developing a tool to automate this
I believe it is connected to a Data Table which must somehow define the various levels of dropdowns

I'm not sure how close it is to being completed
 
Hui said:
MITchip

You may also want to contact member JeffreyWeir by a Conversation
http://forum.chandoo.org/members/jeffreyweir.5316/
He is the resident Drop Down guru

He is developing a tool to automate this
I believe it is connected to a Data Table which must somehow define the various levels of dropdowns

I'm not sure how close it is to being completed
Click to expand...

Thanks, Hui. I pinged him, so hopefully he can help me confirm I'm on the right track, or better yet tell me there is a much easier way to accomplish the goal.
 
Hi Chip. Couple of questions for you:
  1. In your sample file, there is only one choice possible for Level1, which is "One". I take it in the real world example, there will be more than one choices the user can make from the Level1 dropdown
  2. Where is the source data for the dropdowns drawn from? A database? Or is it just stuff put in the sheet manually?
 
jeffreyweir said:
Hi Chip. Couple of questions for you:
  1. In your sample file, there is only one choice possible for Level1, which is "One". I take it in the real world example, there will be more than one choices the user can make from the Level1 dropdown
  2. Where is the source data for the dropdowns drawn from? A database? Or is it just stuff put in the sheet manually?
Click to expand...

1 - yes, real file also has multiple choices for level 1
2 - on the sample file it is just stuff put in manually

Since posting, I have gone ahead and made 2 macros, one that creates and names all of the possible ranges and a second that creates it as one big table instead of individual named ranges. With both scenarios I am at the same impasse - INDIRECT() is far too volatile and I can't get the SubList validation to work. I can add the MainList/SubList range names just fine, and I can get the validation to work for MainList, but when I try to do the validation for SubList I get the 'Source evaluates to an error...' message.

This is what I'm using for SubList (my table is indeed named Table1)
=IF(OR(Sheet2!G5="Choose…",Sheet2!G5=""),"",INDEX(Table1,1,MATCH(Sheet2!G5,Table1[#Headers],0)):INDEX( Table1,COUNTA(INDEX(Table1,,MATCH(Sheet2!G5,Table1[#Headers],0))),MATCH(Sheet2!G5,Table1[#Headers],0)))
 

Attachments

  • BigDynDropSetup2.xlsx
    46.9 KB · Views: 1
@MITchip what and how did You edit?
Of course, there are 'tight' rules how that works.
If You change the format of You data, it's more that possible that no work.
Can You send Your version?
 
Do Your drop-downs work?
What will happen, if You'll click it again.
... and in the beginning, You have to select 'Level1' too
... and only possible Level's drop-downs are active.
... and if You like better, it's possible to 'clean' columns A:D. There are too many times 'One' .. 'Two' and so on, but it need change the code, of course.
Check that too
 

Attachments

  • BigDynDropSetup1.xlsm
    63.3 KB · Views: 3
Last edited:
MITChip: There are two things wrong with your sample file. firstly, you have misunderstood how to set up the master table...the names across the top must exactly match the names of things in your sublists. Secondly, your sample data contains duplicates at the Level3 and Level4 levels, which this approach won't handle in any case.

So you cannot use this table approach to accomplish what you need.

That said, you can use the approach in the file I posted at this thread:
forum.chandoo.org/threads/dynamic-drop-downs-with-large-table.18496/
 
@jeffreyweir Crashes? Why?
Could You test this?
I took away 'Workbook_Open' (file BigDynDropSetup1.xlsm),
but now, You have to press [INIT] before continue and
remember to do the 1st selection with 'Level1'.
I hope that You could test this too.
and about #13 reply ...
My both versions work with datas from columns A:D,
I haven't found any challenges and
here, with Excel2011, all seems to work as well as possible.
 

Attachments

  • BigDynDropSetup1.xlsm
    68.6 KB · Views: 2
Yes. Interesting ...
I try with another format..
 

Attachments

  • BigDynDropSetup1.xlsb
    64.6 KB · Views: 0
jeffreyweir said:
MITChip: There are two things wrong with your sample file. firstly, you have misunderstood how to set up the master table...the names across the top must exactly match the names of things in your sublists. Secondly, your sample data contains duplicates at the Level3 and Level4 levels, which this approach won't handle in any case.

So you cannot use this table approach to accomplish what you need.

That said, you can use the approach in the file I posted at this thread:
forum.chandoo.org/threads/dynamic-drop-downs-with-large-table.18496/
Click to expand...
 
Correct on the setup of the master table, I forgot to remove the part of my macro that gets rid of characters that can't be used in named ranges (since that was my first approach).

I did figure out a way to do it with the table structure...the difference is that I cannot use SubList as the validation for levels 3 and 4, instead I have to create additional names of SubList2 and SubList3 and they have to check the concatenation of all the fields to the left, rather than just the field immediately to the left.

The attached file has my solution.
Don't worry about the 'Choose' fields not showing automatically, I am still midstream in that process. I will upload a final example after I have that part built in.

Thank you Jeff and vletm for your help.
 

Attachments

  • BigDynDropSetup3.xlsx
    47.5 KB · Views: 7
@MITchip
... or You need only four 'SubLists' like in my samples, ver2 could be nicer.
You could use E-column for 'What to do next...'.
 
Final example file using multiple sublists that MATCH based on all previous selections in that row, not just the selection immediately to the left.
 

Attachments

  • BigDynDropSetupFinal.xlsm
    64.1 KB · Views: 4
You must log in or register to reply here.
Back
Top