I am looking to create a series of drop down lists that are dependent on each other. I cant seem to eliminate duplicates in the list either. A user will choose a "Brand Name", which will activate the next drop down "Product line", which will activate the next drop down "Model #", which will return the appropriate bolt pattern. Any help is much appreciated. I am doing this for a senior project.
Have not (yet) looked at your file, but the attached example does what you want. It uses names ranges for all levels and sub-levels, and then uses INDIRECT to return the items needed. See if you can adapt it to your needs - if not, give me a shout
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
The file has 180 rows. The 1st list only has 5 names that should be in the list, but I cant remove the duplicates
see Create Dependent Drop Down Lists
Sorry Ford, I'm interrupting
Last edited by sandy666; 03-30-2017 at 01:34 AM.
I can make the dependent drop downs, but the 1st main list shows 180 items, when in reality there are only 5. I cant separate out the items that are repeated in the list.
There is a few ways to do it, first: copy original list to first "free" column, use DATA ==> Data Tools ==> Remove Duplicates and use this list
or
use this one (for the firsrt column of your example) =INDEX($A$3:$A$182,MATCH(0,COUNTIF($F$4:F4,$A$3:$A$182),0)) confirm by Control+Shift+Enter and drag down as far as you see #N/A
Last edited by sandy666; 03-30-2017 at 01:47 AM.
did you get a chance to look at the file I uploaded? If I go that route, will I still be able to narrow it down to my final objective of displaying the "bolt pattern"
Edit: So when I choose "Okuma" on my 1st drop down, I want only the "Okuma" "Product Line's" to be available in the 2nd drop down, and so on and so on
Last edited by jra81882; 03-30-2017 at 01:49 AM.
Im not going to create your lists for you.
1. Create a list of all Brands
2. Create a list of all lines
3. Create a list of all model numbers
Not sure if you need to create a list of all patterns, that might be the output you want
Do that, and we can take another look at your file
Sandy, go for it, I have no problem with other members answering on a thread
Yes i get a #N/A when I use that Index function, but im very confused as why to use that. I should use that Index function in cell F3?
look at Lists Tab with unique lists
the rest is up to you
see att.
Sandy, you have probably already done some of this?
jra, this is what I meant - I have made a start for you and created the 1st 2 levels, you now need to create the 3rd level for each 2nd-level item
Okuma Mazak Daewoo Mori Seki Haas Okuma Cadet Nexus BMT CL ST Mazak Captin QT Lynx ZL DS Daewoo ESL Bridgeport TT DT SL Mori Seki Heritage QTN Puma SL HT Haas LB SQT Doosan VL TL LU Romi ZT Crown Dual Turn Duraturn L Duplomatic NLX Genos Smart Frontier LC Slant TL LFS Samsung H MSL HB NT NZ DURA NL Feeler
Thank you Sandy and Ford. I guess im just very confused with how to filter out only the Okuma product lines when Okuma is chosen from the list. hahahaha i thought i was pretty good at excel until i came across this problem....
ahhhhh i see. Do i create a seperate table for each 3rd level item and then repeat for the 4th level?
yes, you do - and from the looks of it, that is going to be a fairly tedious job for *you*Originally Posted by jra81882
I have made a start with the 1st 2 dropdowns for you
(love the big pond you guys have up there by the way, visited there a few years ago in the winter, awesome ice waves *i think they call them?*)
Ford, I did almost everything (to second level) but I think OP should read your example file again or Contexture site.
Solution with Names is fast and good I think
Thank you, much appreciated! Erie, is a bitter cold place to visit in the winter! Along the coast, ice dunes are formed. They are actually very dangerous to walk on because they are normally hollow in the middle. Common occurrence for climbers to fall through!
Ice dunes, yes that was it, very strange looking things.
But I think there is no need to duplicate the built-in feature.
1. Click on Brand Name
2. Go to DATA=>Sort&Filter=>Filter
3. Select from Brand Name any brand
4. then select from the Product Line - product
5. etc...
6. to back to the full table, simply un-click Filter on DATA Tab
You save time and work
Sandy, while I would agree with you, by creating a drop down, you give the ability for newbie users to select items, and to avoid typos and make sure they only select specific items
For me, IMHO, Filter feature is more flexible than creating DV lists but this is users's choice
See attached:
I created named ranges for Product Line and Model Number for each Brand
I then created two dynamic ranges to return Product Line (PL) and Model Number (MN) for the selected Brand
In Sheet2
For PL
=IFERROR(INDEX(INDIRECT(SUBSTITUTE(Okuma!$F$3&"_PL"," ","_")),MATCH(0,COUNTIF($B$1:B1,INDIRECT(SUBSTITUTE(Okuma!$F$3&"_PL"," ","_"))),0)),"")
For MN
=IFERROR(INDEX(INDIRECT(SUBSTITUTE(Okuma!$F$3&"_MN"," ","_")),SMALL(IF(INDIRECT(SUBSTITUTE(Okuma!$F$3&"_PL"," ","_"))=Okuma!$G$3,ROW(INDIRECT(SUBSTITUTE(Okuma!$F$3&"_PL"," ","_")))-ROW(INDEX(Brand_Name,MATCH(Okuma!$F$3,Brand_Name,0)))+1,""),ROWS($1:1))),"")
Enter both with Ctrl+Shift+Enter
On Sheet1
Brand, Product Line and Model Number are Data Validation List
Bolt Pattern uses ..
=VLOOKUP($H$3,Okuma!$C$3:$D$182,2,0)
This assumes that for a given Model Number (within Brand) the Bolt Size is the same
Hi
You can use Aggregate formula in Lists sheet as you are on MS 2016?
Okuma Sheet you can add on or remove stock up to 500 rows?
If need more than 500 rows? Just change formula 500 to 1000 then copy down
See the files
Regards
To help you by my post? it would be nice to click onto say "Thank you".
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks