How do I make a Drop down return values based off dates inputted on other columns

WillLuecke

I am making a tracker and for each project, the column 2 is what requirements are left to be done on that project. All the other columns will be date columns, and when I input a date I want it to remove that option from column 2.

Example: If my Project in column 1 is Ford Truck

Column 2 Requirements are - Brakes, Oil Change, Lights

Column 3 - Date column labeled Brakes

Column 4 - Date Column labeled Oil Change

Column 5 - Date column labeled Lights

When I input a date under that column I want it to take away options from my drop down so if I put a date under Brakes the requirements(column 2)drop down will now display as Oil change and Lights. When I input a date on Lights as it’s completed, I want it to then take away that from drop down column 2 meaning the only remaining requirement will be Oil Change

Helppp !

jmyzk_cloudsmart_jp

Hi @WillLuecke

Currently, Smartsheet does not support dynamic changes to dropdown list options based on other cell values or conditions directly within the platform. Smartsheet dropdown lists are static, meaning their options do not change automatically in response to other data changes in the sheet.

The demo sheet below shows a possible workaround.

https://app.smartsheet.com/b/publish?EQBCT=d911281f2584467ebac9230719d17b37 (Link to the demo sheet)

Just show what requirements are left to be done.

The Tracker column shows the requirements left to be completed. You can not select values like in a dropdown list; it just shows what's left.

[Tracker] =JOIN(COLLECT(DISTINCT({List Range : List}), Brakes@row:Lights@row, NOT(ISDATE(@cell))), CHAR(10))

Dropdown List Alternative

The Dropdown List Alternative column shows the combination of a number and the list item, like "01: Brakes." If you choose a number from a dropdown list, the Next Work column shows the list item selected.

List sheet

The list sheet lists list items. The list number needs to be the same as the first sheet using this sheet. (Three, in this case. For demo purposes, I also added a Longer List column.)

https://app.smartsheet.com/b/publish?EQBCT=93de47c487ff45658db7bb485b4127cf

Dropdown List Alternative Sheet

This sheet creates a list with numbers and actual choices with the following formulas;

[List] =IF(COUNT({List Range : List}) >= [No.]@row, (RIGHT("0" + [No.]@row, 2) + ": ") + IFERROR(INDEX({List Range : List}, [No.]@row), ""))

[Linger List] =IF(COUNT({List Range : Longer List}) >= [No.]@row, (RIGHT("0" + [No.]@row, 2) + ": ") + IFERROR(INDEX({List Range : Longer List}, [No.]@row), ""))

https://app.smartsheet.com/b/publish?EQBCT=8f8d23e5be63491391bc6b31ee54a91e

The formulas in the first sheet

Those formulas retrieve the alternative list from the second sheet, and when the number is selected, it shows the Next Work.

[Dropdown List Alternative] =JOIN(COLLECT(DISTINCT({Drop Down List Alternative Range : List}), Brakes@row:Lights@row, NOT(ISDATE(@cell))), CHAR(10))

[Next Work] =IFERROR(INDEX({List Range : List}, VALUE([Chose Number]@row)), "")