Forum Discussion

werner77's avatar
werner77
Copper Contributor
Feb 12, 2021

multiple dynamic drop down lists

Maybe I'm thinking too much into this, but could someone point me in the right direction? I have a file that's over 10 yrs old and needs some updating, needless to say it is beyond my ability and th...
excel
mtarler's avatar
mtarler
Silver Contributor
Mar 17, 2021

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.

werner77's avatar
werner77
Copper Contributor
Mar 17, 2021
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.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Mar 17, 2021
    hmm, it changes for me and I specifically didn't use any of the 'new' functions. The number change is very small and subtle: e.g. 044420 vs 044320.
    The answer to the best way to do this would involve a major restructure of all the data into a master table that you can filter and/or pivot but I doubt you are interested in doing that. So I made the formula fit the way the sheet is set-up.

Resources