HELP ON FORMULAS

hderryberry

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

hderryberry

Any help will be appreciated 

Eid E. Eid

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")))) 

Paul Newcome

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"))))

hderryberry

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

hderryberry

It is telling me invalid operation 

Andrée Starå

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.

More info: https://help.smartsheet.com/function/if

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

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"))))

hderryberry

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

Andrée Starå

Happy to help!

Can you paste the exact formula you're using?

Paul Newcome

What Andree said... ^^

hderryberry

=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.

Eid E. Eid

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

 

 

Andrée Starå

No worries!

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

Did it work?

hderryberry

That worked. Thank you so much!!!!

hderryberry

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