HELP ON FORMULAS

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

2019-10-04 (2).png

Find more posts tagged with

Construction

IT & Operations

Comments and Conversations

Comments

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!!!!

Sign in to join the conversation!

Quick Links

Trending discussions

Automated email with number of pending records
Hello, I use Smartsheet in such way that people are asked to provide data on many records and then that data needs to be approved by one other person (always the same person). I would like to set up an automation that, each morning, checks to see if there are records waiting to be approved, and if there are, sends an…
How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
License Question
I already have a Smartsheet license with my company, but I was recently invited to collaborate on a sheet owned by another group within the same organization. When I accepted, I was told I need to upgrade in order to edit or contribute. Can you please explain why this is required? It seems unreasonable to need multiple…