Google-sheets – How to combine FILTER with TRANSPOSE in data validation custom formula

data validationfilterformulasgoogle sheetsgoogle-sheets-data-validation

Cells A2, C2, and E2 contain the data I want to make my custom list for data validation. Cells B2 and D2 have data I don't want in the list (just text – "vs" – in them).

So, I'm trying to use the following formula to get the custom list in G2. Whatever is entered in A2, C2, and E2 is what I want to dynamically become my custom list to choose from in G2.

EDIT: Ultimately, A2 – G2 cells will be copied down (G2 containing the custom list of A2 – E2 without the "vs" cells being listed) and each row should act the same as above it but each dynamic and unique to their own.

Here's what I'm using and it's not erroring but also not making my list I want:

=(TRANSPOSE(FILTER(A2:E2,A2:E2 <> "vs")))

Any help is appreciated and alternative methods are welcome if they work!

Best Answer

  • don't use it like this:

    9

  • first, you need to create your custom list anywhere where it won't hinder you (like here on column G or even placed on hidden Sheet2):

    =TRANSPOSE(FILTER(A2:E2, A2:E2 <> "no"))

    9

  • and then you can create data validation from a range (G2:G in this case):

    8

Related Topic