2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Formula for cascading filter options

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?

submitted by /u/Kermie88
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#Excel alternatives for data analysis
#generative AI for data analysis
#row zero
#natural language processing for spreadsheets
#AI formula generation techniques
#rows.com
#formula generator
#big data management in spreadsheets
#conversational data analysis
#Excel compatibility
#cloud-based spreadsheet applications
#real-time data collaboration
#intelligent data visualization
#data visualization tools
#enterprise data management
#big data performance
#Excel alternatives
#data analysis tools
#data cleaning solutions
#cascading filter