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

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. 

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.

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!