How do I create dynamic dropdowns that works 2 ways?
Our take
I have an Order Form sheet set up with a fair bit of code in the background. Part of the Form has order lines with headers of Code, Category and Product Description. I have a separate Products list which I wanted to create the dynamic drop down lists using formulas.
I soon found out that you cannot type a FILTER formula directly into the Excel Data Validation source box. After a bit of researching, I figured how to to create dynamic dropdowns by referencing a spill range.
The way I've set it up is probably a bit messy (but it works). At the moment the user has to select the category of product they want first. Then the drop down list in Product Description will show only items in that category. When they select the item they want, the drop down list for Code will show the specific code that item relates to.
It can work but I was hoping to be able to use partial searches in any of the 3 columns to generate suggestions. eg. Start typing partial item name which would then narrow down options in the drop down list for Products as you type each letter. Select the item and the category and Item Code self populates. Also enable the user to partially type item code or category for a similar result.
Hope this makes sense. And if so, any ideas?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Formula for cascading filter optionsI'm building a quoting tool that has dynamic pricing based on options selected via drop downs. However, some (but not all) drop downs require cascading as the options available will be different depending on what's selected in an earlier drop down (in the same row). Here's the formula I'm using: =SORT(UNIQUE(FILTER(SizeKits[KIT],SizeKits[SIZE]='Other Sheet'!C13))) SIZE and KIT are named ranges. Depending on SIZE selected, it dictates the drop down options presented in KIT drop down. I've then got a lookup table referenced that has all of the options for KIT available depending on what SIZE is selected. Then I've got Data Validation to point to this formula on the "helper" sheet. This formula is on a "helper" sheet, where the quoting happens on 'Other Sheet'. The problem that I'm running into is that it's directly referencing cell C13 in Other Sheet for the other rows. There are seven rows of cells where users can select different product options, but I'm not sure how to make each row independent of each other. For example, C14 should have it's own options available depending on the other options selected in row 14. How do I make each row independent of each other? submitted by /u/Kermie88 [link] [comments]
- Change contents of cells based on a drop downSo, I'm trying to generate a spreadsheet to make the forms we use in the office to issue keys. When people are given an apartment, we have a sheet with all the key numbers on it, and then they sign for it. At the moment, we've got 22 different PDFs and Word documents, and it doesn't work. So, what I want to do is this. I've built the template in Excel and created a Reference sheet which will have all the numbers in it. What I am trying to do is set it up so that when you use the Apartment number from a dropdown, it populates with all the key numbers. This sounds like it should be easy to do, but my Excel brain is broken. Advice would be much appreciated! submitted by /u/Hot_Syrup_1774 [link] [comments]
- Returning multiple matches per ID (auto-fill when dragged down?)Hi there! I have multiple assigned IDs for a single code so I need to return all matching values, not just one. I also want this to be kind of automated (?) when I drag down the upstream ID column, it can pull all corresponding values, even if they share or don't share the same code I tried using FILTER, but it only spills results in one place and doesn’t carry over properly when dragged down for other rows. I don’t want to adjust ranges every time manually since my data might get longer. Is there a way to make a formula that: works when dragged down, returns multiple matches per ID, and still updates properly even if the data goes beyond the current rows? Left Side = source workbook; Right Side = destinatioon workbook Thank you so much in advance! submitted by /u/jeankrstein [link] [comments]
- Building a dynamic formulaI'm using Excel to predict the capacity usage of storage devices. It's clunky, but it works great until someone changes the name of the SharePoint folder I'm using to reference my data. (This has happened twice) I had the brilliant idea (or so I thought) to dynamically build the formula using "concat", which I thought would allow me to simply change one cell and be able to affect all of my entries (about 65 rows). I then learned that "concat" only builds the formula, it does not evaluate it and that I needed to use "indirect" to evaluate it. That worked, except my output was "REF", not what I was expecting. After a little more digging, I found that "indirect" does not work with external workbooks that are not open on the local machine. (I don't want to open 60 workbooks to get the data I need) It looks like I am out of luck, but I am hoping someone else has a brilliant idea I have not considered yet. Thanks submitted by /u/Separate-Tomorrow564 [link] [comments]