Search code examples
javascriptgoogle-sheetsgoogle-sheets-formula

How do you utilize a 'Data Validation' drop box as a 'Filter' condition?

Slightly complex formating, but I hope someone can direct me in the right direction.

Refer to this Demo Sheet before reading as I'll be referring to it throughout this post.

What I'm Trying to Accomplish: I'm trying to 'Filter' the "Ongoing Sales" sheet based on which store is selected (G13). I have a formula that will automatically populate the rows in each branded section and would like to add another condition to that formula stating the following:

  • If 'G13' is found in 'Current Markdown Sheet'!$P$16:AC16, display/filter the corresponding column if the value's in said column are greater than 0.

My current code (Which is filtering 'SKU's based off of brand names and if its men's, women's, or kids):

=FILTER('Current Markdown Sheet'!$B$16:$B, REGEXMATCH('Current Markdown Sheet'!$A$16:A, "ASICS"), REGEXMATCH(LEFT('Current Markdown Sheet'!$C$16:C,2), " M"))

Here are some visuals if my explanation wasn't good enough 😂:

^ Where I want the condition to go. (You can replace the &T(N("INSERT FILTER CONDITION HERE"))) ^

enter image description here enter image description here enter image description here enter image description here

Some Rules to Follow:

  • You cannot alter the "Current Markdown Sheet" in anyway (Add data, remove data, etc).
  • I dont necessarily need it to show "All Stores", but if it's possible; Bonus points 😉.

Best of luck everyone, and I thank you in advance! Cheers!

Solution

  • Try:

    =FILTER('Current Markdown Sheet'!$B$16:$B, REGEXMATCH('Current Markdown Sheet'!$A$16:A, "ASICS"), REGEXMATCH(LEFT('Current Markdown Sheet'!$C$16:C,2), " M"),INDIRECT("'Current Markdown Sheet'!$"&regexextract(ADDRESS(16,MATCH(G13,'Current Markdown Sheet'!16:16,0)),"[A-Z]+")&"$16:$"&regexextract(ADDRESS(16,MATCH(G13,'Current Markdown Sheet'!16:16,0)),"[A-Z]+"))>0)

    Result: enter image description here

    Explanation:

    -Using the MATCH() to find the column of the match word from the dropdown, this returns the column index. You then use the ADDRESS() to get it's exact cell address, then REGEXTRACT() to only get the COLUMN letter:

    regexextract(ADDRESS(16,MATCH(G13,'Current Markdown Sheet'!16:16,0)),"[A-Z]+")

    -Now that you have the column, you can use the returned column to filter those greater than 0. You can also use column for the INDRECT() to refer to the dropdown.

    References: