I’m working on a Google Sheets project that uses several interconnected dropdowns to filter data. The main formula in my worksheet has become extremely complicated and hard to manage. It combines multiple dropdown selections but the formula is getting really long and messy.
The issue is that each dropdown depends on others, so I’m not sure if I can break it into smaller parts. I’ve avoided using the built-in filter feature because it doesn’t give me the control I need for this specific use case.
I also have a secondary problem with my rating filter. Here’s the formula I’m using:
When I select any rating option from the dropdown, it displays text entries along with numbers, but I only want to show numeric ratings. How can I filter out the text values and keep only the numbers?
Any suggestions for cleaning up these formulas would be really helpful. Thanks!
try wrapping your rating check with ISNUMBER() function to filter out text values. something like IF(AND(ISNUMBER(Products!M1:M200), Products!M1:M200>=4), Products!M1:M200, "") for the 4+ rating part. this should eliminate text entries and only show actual numbers that meet your criteria.
One approach that worked well for me with similar complex formulas was creating named ranges for each dropdown dependency. Instead of referencing raw cell ranges, you can define names like ‘CategoryFilter’, ‘RatingFilter’ etc. This makes the main formula much more readable and easier to debug when something breaks.
For your specific rating issue, I’d suggest using QUERY function instead of nested IFs. Something like =QUERY(Products!M1:M200, "SELECT * WHERE M IS NOT NULL AND M >= "&VALUE(LEFT(M5,1))) handles both the text filtering and rating comparison in one go. QUERY automatically ignores text values when you use numeric comparisons, which solves your text display problem without needing ISNUMBER checks.
The real game changer though is building your dropdowns using data validation with custom formulas that reference the previous dropdown selections. This way each dropdown automatically updates based on what’s actually available in your filtered dataset, reducing the complexity of your main formula significantly.
Breaking down complex formulas into helper columns is usually the best approach for maintainability. Even with interdependent dropdowns, you can create intermediate calculations in hidden columns that feed into your main formula. For your nested IF structure, consider using a combination of FILTER and WHERE functions instead - they handle multiple conditions more elegantly than stacked IF statements. Regarding the text filtering issue, you need to add an ISNUMBER condition before your rating comparison. Your formula should look something like IF(M5="All", FILTER(Products!M1:M200, ISNUMBER(Products!M1:M200)), FILTER(Products!M1:M200, AND(ISNUMBER(Products!M1:M200), Products!M1:M200>=VALUE(LEFT(M5,1))))). This filters out text entries first, then applies your rating criteria. The VALUE and LEFT functions can help you extract the numeric part from your dropdown selections dynamically rather than hardcoding each rating level.