# HELP ON FORMULAS

Options
✭✭✭✭
edited 12/09/19

I am trying to create a formula that will change the production status automatically. My [production status] column is a drop down box with these options "Roof Ordered, Roof Delivered, Roof Building, Roof Complete". My other columns are date columns, so when the column [Roof Delivered on] has a date in it I want it to change the production status to "Roof Delivered. I created this formula in the [production status} column =IF(ISDATE([Roof Delivered On]@row), "Roof Delivered") and that worked but how do I add the other columns ([Roof Ordered On], [Roof Building On],[Roof Complete On]

Thank You

Heidi

«1

• ✭✭✭✭
Options

Any help will be appreciated

• ✭✭✭
Options

Try this :

=IF(ISDATE([Roof Delivered On]@row), "Roof Delivered", IF(ISDATE([Roof Complete On]@row), "Roof Complete",IF(ISDATE([Roof Building On]@row), "Roof Building",IF(ISDATE([Roof Ordered On]@row), "Roof Ordered"))))

• ✭✭✭✭✭✭
Options

You are probably going to want to adjust the order of your nested IF's.

=IF(ISDATE([Roof Complete On]@row), "Roof Complete",IF(ISDATE([Roof Building On]@row), "Roof Building",IF(ISDATE([Roof Ordered On]@row), "Roof Ordered", IF(ISDATE([Roof Delivered On]@row), "Roof Delivered"))))

• ✭✭✭✭
Options

Ok just trying to understand...what you mean when you say "adjust the order"

• ✭✭✭✭
Options

It is telling me invalid operation

• ✭✭✭✭✭✭
Options

Hi,

It means the order of the different IF parts in the formula. When something in the formula is true it won't look at the rest of the formula. That's why the order is important.

Usage Notes

• You can nest IF functions to perform multiple logical evaluations.
• Smartsheet reads the IF statements in the formula from left to right, displaying a value based on the first expression to evaluate to true. When nesting IF functions, the optional return_if_false will be returned only if all logical expressions evaluate to false.

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

Andree is correct.

Basically, the formula stops running as soon as it finds its first "true" statement. So if your first statement is checking if [Roof Delivered On]@row is a date, it will not run anything after that check as soon as that cell is in fact a date.

This means that if that is the first thing that happens, it will always say "Roof Delivered" even if there are other dates populated.

To account for this, we work backwards from our last step to our first step.

.

While typing this up, I also realized that I probably didn't get the order quite right in my previous post. Here is an updated version.

=IF(ISDATE([Roof Complete On]@row), "Roof Complete",IF(ISDATE([Roof Building On]@row), "Roof Building", IF(ISDATE([Roof Delivered On]@row), "Roof Delivered", IF(ISDATE([Roof Ordered On]@row), "Roof Ordered"))))

• ✭✭✭✭
Options

Great thanks that makes sense to me now and I did that and it is still telling me invalid operation....any other suggestions

• ✭✭✭✭✭✭
Options

Happy to help!

Can you paste the exact formula you're using?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

What Andree said... ^^

• ✭✭✭✭
Options

=IF(ISDATE([Roof Completed On]@row), "Roof Complete", =IF(ISDATE([Roof building]@row), "Roof Building", =IF(ISDATE([Roof Delivered On]@row), "Roof Delivered", =IF(ISDATE([Roof Ordered On]@row), "Roof Ordered"))))

Sorry it took so long, but hopefully you guys can still help me.

• ✭✭✭
Options

Just remove all the "=" except the first one.

• ✭✭✭✭✭✭
Options

No worries!

Eid is correct! Remove all = except the first one.

Did it work?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭
Options

That worked. Thank you so much!!!!

• ✭✭✭✭
Options

It did work! Thank you so much!!!!