Nested if()

Hello great community.

I'm new in Smartheet and I've a problem and even don't know if a solution exists. I'm trying to create a sort of automatized timesheet for my team.

I would like them to connect to the same sheet. They have to select their name in a list ("Team" column), and the type of mission they've managed in an other list ("Mission" column). But each one doesn't cost the same per day, and the cost is even different depending on the mission.

So, I would like that the cost / day changes automatically ("Cost/day" column) depending on what is selected on "Team" column and "Mission" column.

I hope I'm clear... I tried with nested if() but it doesn't work. If you have a solution it would be really great for me.

Please find below the screenshot and my formula. Thanks a lot for your help. Have a nice day.

=IF(Team1 = "Charlotte"; IF(Mission1 = "Conception"; 400; IF(Mission1 = "Production"; 300; IF(Team1 = "Géraldine"; IF(Mission1 = "Conception"; 1000; IF(Mission1 = "Production"; 800))))))

Find more posts tagged with

Human Resources

Finance

Formulas

Reports

Project Management

Comments

Nic Larsen

This should give you a start on how to build your nested formula, since I don't know how many combinations or conditions you have.

=IF(AND(Team@row = "Charlotte"; Mission@row = "Production"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Conception"); 400; IF(AND(Team@row = "Geraldine"; Mission@row = "Production"); 800; IF(AND(Team@row = "Geraldine"; Mission@row = "Conception"); 1000))))

You may be able to shorten this with some OR statements if some combo's are the same.

Ptipok

Hello,

yes it's perfect. Thank you so much for your help and your quick answer

Have a nice day

Ptipok

Dear Nic,

I tried to follow your advice to shorter my fomula, using OR but finally, it doesn't work. For sure, I don't use the right method...

Indeed I have certain mission that cost 0. So I tried this :

=IF(AND(Team@row = "Charlotte"; Mission@row = "Conception"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Production"); 500; IF(AND(Team@row = "Charlotte"; Mission@row = "Gestion de projet"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Déploiement"); 400; IF(AND(Mission@row = "Mission interne", IF(OR(Mission@row = "Consulting interne", Mission@row = "Avant-vente"); 0;))))))))))

Thanks a lot to help me again. Have a nice day

Nic Larsen

So find it helps at times to split out each of your IF statements to make sure they all line up and then re-stack them.

When I did that on your formula, I found an incomplete IF formula. I also moved your OR statement to the front as the initial check.

= IF(OR(Mission@row = "Consulting interne", Mission@row = "Avant-vente"); 0; IF(AND(Team@row = "Charlotte"; Mission@row = "Conception"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Production"); 500; IF(AND(Team@row = "Charlotte"; Mission@row = "Gestion de projet"); 800; IF(AND(Team@row = "Charlotte"; Mission@row = "Déploiement"); 400;

IF(AND(Mission@row = "Mission interne", ???

I think you need to complete that piece, and add it to the end after 400; and then give it another try. Unless I'm not understanding your initial OR statement.

Ptipok

Hello Nic,

It works. Thanks a lot again.

Have a nice day.

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
How can I set up recurring tasks in Smartsheet?
I need tasks that repeat (daily, weekly, monthly, yearly). Each should show a due date and a “Completed” checkbox. When I check it off, I’d like the task to log in an archive sheet with the completed date and then reset on the main sheet with a new due date and cleared checkbox. Has anyone found a simple way to do this?
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…