I have a Google Sheet that has a cell that I want to use as a drop-down filter for another range within the sheet.
You can take a look at the sheet

To something like this:

I've tried using Named Ranges, using a seperate sheet to filter the data, and a couple other methods I found. Nothing seems to work exactly how I want it to, like in picture #2.

CodePudding user response：

try:

=INDEX(LAMBDA(x, SPLIT(FLATTEN(SPLIT(FLATTEN(QUERY(TRANSPOSE(x),,9^9))&"×​", "×")), " "))
 (QUERY({ROW(Data!A2:D), SORT(Data!A2:D, 3, 0)}, 
 "select Col1,Col2,'♀',Col3,Col4,'♂',Col5 where Col3 = '"&G11&"' label'♀''','♂'''")))

UPDATE:

=INDEX(LAMBDA(x, SPLIT(FLATTEN(SPLIT(FLATTEN(QUERY(TRANSPOSE(x),,9^9))&"×​", "×")), " "))
 (QUERY({ROW(Data!A2:D)-1, SORT(Data!A2:D, 3, 0)}, 
 "select Col1,Col2,'♀',Col3,Col4,'♂',Col5 where 2=2 "&IF(
 REGEXMATCH(G11, "(?i)all|n\/a|^$"),," and Col3 = '"&G11&"'")&" label'♀''','♂'''")))