Dropdown... recursive problem / dropdown without data validation possible?

0
votes

I have 3 columns: (example)

  • Column A: elements for the dropdown
  • Column B: "VIP" selection
  • Column C: "normal User" selection

The dropdown in Column B simply has all elements of Column A.

The dropdown in Column C however should only have the remaining elements of Column A (=> not selected In B or C)

I was able to do that by creating a Column (lets call it D) where only elements are added that are in Column A but not in Column B or C and using the data validation for Column C on this Column D. The Problem is ofc this is recursive...so as soon as I enter something in Column C it gets removed from Column D and is no longer valid in column C (but for this Cell it should be)

So I think there are only 3 options to solve this...

  • don't use recursion (but I don't know how to solve my problem then)
  • disable the warning from the data validation (would be fine for me but as far as I know not possible?)
  • create a dropdown without using the data validation (don't know how/if that is possible)

I hope someone can help me with that.

2
validationgoogle-sheetsfilterdropdowngoogle-sheets-formula

2 Answers

0
votes

you could solve this with 2 side columns which you can then hide. column E will feed the dropdown of A1 and B1. and then column F will feed C1 where F2 will contain:

=FILTER(E2:E, E2:E<>A1, E2:E<>B1)

0

0

0

spreadsheet demo

0
votes

I found a solution.
I made a filter for every row with all remaining values ("Column C - Column A - Column B") for that row by using the following formula and simply adding the value in Column B from that row again at the end to that filter. (values are Column E - M)
=IFERROR(TRANSPOSE({FILTER($D$3:$D$11, ISNA(MATCH($D$3:$D$11, $A$3:$A$11, 0)), ISNA(MATCH($D$3:$D$11, $B$3:$B$11, 0)));B3}),"")

The dropdown in Column B is now Column E - Column M (end Column depends on how many possible values you have)

(for B3 it would be E3:M3),

(for B6 it would be E6:M6), ...

Screenshot: https://i.stack.imgur.com/b9UVb.jpg (sry I'm not allowed to post a picture.I hope the link will still be available...)