Populate a drop down based on date in other columns

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?

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…