Formula for cascading filter options
Our take
I'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?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- Drop-down list dependant on two other cellsI have a file where I want a drop-down list in cell D14 that is based on cell D10 and D12. I have looked online and saw lot of videos that are dependant on one cell with the indirect formula, but not two. https://preview.redd.it/ptykf9yzrrwg1.png?width=289&format=png&auto=webp&s=6c0fe00c73c3d45816229bcba5fe2f1733bff083 So for example, if I had this random data and I chose Animal in cell D10, Red in cell D12, I would expect to see 33 and 2 in the drop-down menu of cell D14. Is this possible? The data is in another tab called Data and the info is in column F (F3:F1000) that should appear in the drop-down menu of cell D14. D10 and D12 are both drop-down menus if that matters submitted by /u/LookAtThosePepperoni [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]
- Drop down list from discontinuous cells not workingI have to make a bunch of drop downs referencing a seperate sheet that I cant really edit. some of the lists need to reference cells that arent all contiguous. For instance data validation list on =Sheet1!$A$1,Sheet1$C$1, Sheet!$D$1 yields a "You may not use reference operators for data validation critera" But leaving B1 in there works. i.e. =Sheet1!$A$1:Sheet!$D$1 works. Any way around this? submitted by /u/223specialist [link] [comments]