Change a drop down value when a date is entered

Kristi Curry

Is there a way to change a drop down value from a select list when a date in a adjacent column is entered?

So if an Accounting Escalation Date is provided, I want to auto route it to Accounting Team. Item Owner has 3 values (Accounting Team, Air Operations, Escalations Team)

Andrée Starå

Hi @Kristi Curry

Yes, but you’ll need to account for the other options because if you change the value manually, it will delete the formula.

Try something like this.

=IF(ISDATE([Accounting Escalation Date]@row); "Accounting Team")

The same version but with the below changes for convenience.

=IF(ISDATE([Accounting Escalation Date]@row), "Accounting Team")

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Kristi Curry

Yes, but you’ll need to account for the other options because if you change the value manually, it will delete the formula.

Try something like this.

=IF(ISDATE([Accounting Escalation Date]@row); "Accounting Team")

The same version but with the below changes for convenience.

=IF(ISDATE([Accounting Escalation Date]@row), "Accounting Team")

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Kristi Curry

Makes sense. How do you auto apply the formula to all incoming rows from a form without having to drag it down in the sheet.

Andrée Starå

@Kristi Curry

Either you'll need to make sure that the below is true, or use the new Column Formula feature, and then it just works.

The new row will be auto-filled with the formulas if there are two rows below/above with the same formulas and the same level of hierarchy.

More details:

If the formula structure is the same above/below the Formula Autofill will add the formula(s) automatically.

Conditions That Trigger Formula Autofill

You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:

• Directly between two others that contain the same formula in adjacent cells.
• At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formulas.

More info: 

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

Kristi Curry

I figured out the column formula but now we can't manually override and select a value from the drop down. are these mutually exclusive?

Andrée Starå

@Kristi Curry

Yes, it's all or nothing, so you'll need to add the different needs to the formula depending on the sheet's structure.

Make sense?