IF Function

Zak2428

I've been trying to find a way to combine various checkboxes to designate different status', for example:

If this checkbox is checked then this is the status

However, if that checkbox is checked AND this checkbox is checked, then it's in a different status

If these 3 checkboxes are checked, then it's in a different status

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Zak2428

So what i'm trying to do in this template is to create a formula in the "Status" row (box is outlined) - right below the "On Time" row at the top. The status' are indicated by the black rows with white lettering below: "Information Collection", "Design Queue", "Design Review", etc.

My team uses this template and must check off every single checkbox, so I want to be able to have Info Collection checked, but then when they get down to the Design Queue checkbox, they can check it off as complete and it'll auto-populate the "Status" data cell from Info Collection to Design Queue. Then, when they complete the next step which is design review, they'd check the box and again it'd update the Status cell from Design Queue to Design Review.

Smartsheets Example.PNG

Andrée Starå

Ok.

Try something like this. Change the row number so it corresponds to yours.

=IF(Complete9 = 1; "Design Review"; IF(Complete8 = 1; "Design Queue"; IF(Complete4 = 1; "Information Collection")))

The same version but with the below changes for your and others convenience.

=IF(Complete9 = 1, "Design Review", IF(Complete8 = 1, "Design Queue", IF(Complete4 = 1, "Information Collection")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Zak2428

Hey Andree, thank you so much for the help!! Reversing the order like that works perfectly!

Much appreciated!!

Andrée Starå

Excellent!

Happy to help!

Zak2428

Sorry to bother you again, but maybe you can help me with this one too..

I want this "On Time" cell to have a formula:

If no "Completion Date" is filled in, then the On Time column says "Hold".

If the Completion Date is later than the Due Date, the On Time column says No.

If the Completion Date is before the Due Date, the On Time column says Yes.

I've tried:

IF([Completion Date]8 = 0, "Hold", IF([Completion Date]8>[Due Date]8, "No", "Yes")) - It works for the no completion date = hold, but when i fill in dates it goes #Invalid Operation. Maybe it requires an OR function? I've never dealt with those though.

Smartsheets Example.PNG

Andrée Starå

Happy to help!

Try something like this.

=IF([Completion Date]@row = ""; "Hold"; IF([Completion Date]@row > [Due Dat]@row; "NO"; IF([Completion Date]@row < [Due Dat]@row; "Yes")))

The same version but with the below changes for your and others convenience.

=IF([Completion Date]@row = "", "Hold", IF([Completion Date]@row > [Due Dat]@row, "NO", IF([Completion Date]@row < [Due Dat]@row, "Yes")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Andrée Starå

There was a spelling mistake in the previous one.

Try something like this.

=IF([Completion Date]@row = ""; "Hold"; IF([Completion Date]@row > [Due Date]@row; "NO"; IF([Completion Date]@row < [Due Date]@row; "Yes")))

The same version but with the below changes for your and others convenience.

=IF([Completion Date]@row = "", "Hold", IF([Completion Date]@row > [Due Date]@row, "NO", IF([Completion Date]@row < [Due Date]@row, "Yes")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Zak2428

Yes it did!

Thanks again!!

Andrée Starå

Excellent!

I'm always happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…
INDEX/COLLECT??
Having trouble with an INDEX/COLLECT/CONTAINS formula. I have two sheets, my source and my destination; the source sheet has a column I want to collect into the destination. -Tool ID, is in both sheets and the target of the INDEX/MATCH, the source sheet might have this listed 6 or 7 times -Purpose of Device, there are…