Forum Discussion
multiple dynamic drop down lists
Ok, I see were I went wrong explaining this, my bad. Alright, let me try from the top.
A customer orders an assembly from us that consist of three components (tail + center + tail). These three components are sold in the combined length of all three. so for a 10' I would pick 18"(tail) + 84"(center) + 18" (tail) = 10'(total length). They also have two version's within the tails and centers of split and solid(different part numbers). So what I was hoping to do was as a dropdown's were picked it would remove the part numbers that couldn't be used, ie if you choose 72" all of the other length wouldn't show up in the tail and center drop downs. likewise for the solid and split depending on which one they picked the other would be hidden.
werner77 So you basically want to have some filters. The concept is still the same as what I demonstrated in the previous sheet. You have the user select some drop downs which formulas on the other sheet use to create the desired filtered list and then the part list drop down points at that calculated filtered list. I can't demo it because I still don't see how this works for your situation. I'm not sure if I just don't get it or if it is because you only shared a limited set of data and hence it isn't 'adding' up. But look in that sheet at cell D2 where i create a filtered list based on 1st indexing the area to only return the column based on if it is solid or split, then the FILTER returns only the rows that = the desired length but you will have to do something like >= or <= based on mins or maxs or something.
I hope that puts you on the right track.
I attached the worksheet that we use hopefully this will help explain what i'm trying to do. The right side works but the center section has an error I just can't seem to get around.
werner77 very interesting. so the drop-down menu isn't the problem, it is the formula using that menu to display the options. so the drop down options correspond to named ranges on the other sheet (after you remove the spaces). The list on the right is working because it is not (or no longer) a classic array formula (no curly brackets) and the @ in front works because the rows on the other sheet are the same row numbers of this table (rows 9 - 24). The middle table isn't work (any more?) because it was getting the array but not able to index it properly. If you move the formula (without the curly brackets) down to rows 30-32 it works. Alternatively I added an INDEX function on the original formula so that it will pull the correct value based on which row it is.
I'm explaining all this because it looks like you have a lot more of the sheet that you removed that might need updating and hopefully you will understand more to make that easier for you.
- There was a whole lot more on the sheet but I couldn't put it up. I guess what I'm looking for is a simpler way of using the dropdowns to automatically fill in the data fields with the correct P/N's. I opened the sheet you attached but the data in the upper field wasn't shifting between the different dropdown options.