Populate a drop down based on date in other columns

Heather K

Is it possible to auto-populate a drop down based on a date being entered over a range of columns? 

Example:

Column 1 (Drop Down)          Pending (Date)        Review (Date)        Released (Date)

Pending                                    2/21/19

Review                                     2/12/19                     2/19/19

Released                                 2/12/19                     2/19/19                 2/21/19

 

So what I am looking for is if there is only a date in Pending, then Pending would be chosen from the drop down. But if there is a date in Pending and Review, then Review would be chosen from the drop down. And lastly, if there is a date in all three columns, then Released would be chosen from the drop down. Is this possible?

Nic Larsen

Try this:

=IF(Released@row <> "", "Released", IF(Review@row <> "", "Review", IF(Pending@row <> "", "Pending")))

Heather K

That formula didn't work. It gave the error #UNPARSEABLE.

Nic Larsen

Hmm its working on mine when I set it up. 

You may want to retype it rather than copy and paste it. Also if you are column names are different or require brackets that will need to be added. Do you have a screenshot of your actual 4 columns? I can update it accordingly. 

Heather K

My column names are slightly different but I did update them in the formula when I put them in. I have attached a screenshot of my actual sheet.

Nic Larsen

Okay. I reworked mine to your column names. Be sure all of the columns with dates are set as Date type columns. That was causing issues to mine as I played with it. In your drop down column, make sure you have the names or unchecked the box that restricts responses. 

This might be more than you were looking for but: 

=IF([Implemented Date]@row <> "", "Implemented", IF([Released Start Date]@row <> "", "Released", IF([Implementation Review Start Date]@row <> "", "Implementation Review", IF([Review Start Date]@row <> "", "Review Start Date", IF([Design Change Start Date]@row <> "", "Design Change Review", IF([Quality Review Date]@row <> "", "Quality Review", IF([Pending Start Date]@row <> "", "Pending", "Not Started")))))))

Heather K

That worked. Thank you very much!