1

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!

Improve this question
1
  • can you share a copy of your sheet?
    – user0
    Jan 8, 2019 at 22:16

1 Answer 1

Reset to default
0

  • 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

Improve this answer
7
  • Thanks for the quick answer! However, I may not have made my post clear enough. The data in these columns that I'm transposing is dynamic and each row will be copied down from above it (filling series if you will). So, A2:E2 I will want to become a series of rows that each are unique custom list selected from a source. I am able to do as you suggested just fine with just a single row but since I'd technically need the the transposed filtered list for each row (A3:E3, A4:E4), it ends up becoming pretty messy over time.
    – Ozzie
    Jan 9, 2019 at 20:47
  • docs.google.com/spreadsheets/d/…
    – Ozzie
    Jan 9, 2019 at 20:57
  • That is my google sheets doc (copy of it). The 3P Tracker tab is the one I've started playing with to get those 3 columns (sans the "vs" columns between) to become the "custom list" used for column G data validation.
    – Ozzie
    Jan 9, 2019 at 21:20
  • No problem. G2 needs to be a drop-down list of A2 C2 and E2. I have been approaching it from the data validation angle but maybe that's not the appropriate approach if a custom formula in data validation can't actually create that list needed to form the custom list.
    – Ozzie
    Jan 10, 2019 at 0:47
  • @Ozzie yes, exactly custom formula in data validation can't actually create that list custom formula in data validation is just a kind of extension/offset that checks for additional value. it cant create real output. imagine it like this: you will set data validation for cell A1 that A1 can be number between 1 and 100. now A1 cell cant have one more data validation so you will set data validation from B1 on cell A1 that A1 cant have number between 8 and 88 eg. if A1 will contain nr. 7 all is ok, but if you enter nr 12 then cell B1 will tell you that A1 has value that violates B1 data valid.
    – user0
    Jan 10, 2019 at 0:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.