Formula to remove options already used

I have multiple data cells using rowlinks from a table on the same screen. How do I make options disappear in the dropdown list once they are used in another data cell?

@DJ.Millhouse Cool idea! I just got it working in a demo app.

Here's a table to hold the picklist options.

In the app, add a data cell, make it a variable, and then format it as a rowlink, but set source to Filter, and write a filter formula to only return rows from the table that don't match whatever is in option1 or option2.

Do the same thing for the Option2 data cell.

In the app, I initially have all 3 options.

But after choosing option1, alpha is removed from option2.

And... if I go back to Option1, I can only see Charlie.

1 Like

Thank you! Your solution works, however I have more than 2 options. Is there a way to do it if you have more options? I tried continuing the formula with the additional options, unfortunately it didn't work.

This pattern doesn't work?

No, it only works for the first 2 but after that all options from the drop down list are available.

hi @DJ.Millhouse, I was able to get @AndrewB solution to work for up to 5 different drop downs. Maybe there are a couple of gotchas that are worth mentioning. First here is my Table:

And here is the screen:

Each of the drop downs is:

  • Editable
  • Variable (no shared)
  • Display = Rowlink, Filter
  • The filter functions are expanding (see below)
    DataCell1=Table without any filter
    DataCell2=FILTER(DropList,"DropList[Items]<> $[DataCell1][Items]")
    DataCell3=FILTER(DropList,"DropList[Items]<> $[DataCell1][Items] AND DropList[Items] <> $[DataCell2][Items]")
    DataCell4=FILTER(DropList,"DropList[Items]<> $[DataCell1][Items] AND DropList[Items] <> $[DataCell2][Items] AND DropList[Items] <> $[DataCell3][Items]")
    DataCel5=FILTER(DropList,"DropList[Items]<> $[DataCell1][Items] AND DropList[Items] <> $[DataCell2][Items] AND DropList[Items] <> $[DataCell3][Items] AND DropList[Items] <> $[DataCell4][Items]")

Essentially, each subsequent data cell filters out the choices made before it.

Hope this helps; if we missed something let us know your use case in more detail.
Cheers,
DT.