How do I make a Drop down return values based off dates inputted on other columns
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 !
Answers
-
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.
(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.)
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), ""))
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)), "")
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives