Trying to create an IF THEN function in smartsheet

My example is to select an material size value based on the material. Something like this:

If [cell1]="4X8 Sheet" then [cell2]=32, if [cell1]="4X12 Sheet" then [cell2]=48, etc, etc.

I have a dropdown list in the materials column to restrict the set of possible materials.

Subsequently, I would like to take those values to calculate productivity rates(averages) in another column using the values calculated for cell 2. Something like this:

If [cell2]="32" then [cell3]=32 * [cell4] / [cell5], if [cell2]="48" then [cell3]=48 * [cell4] / [cell5], etc. etc.

I tried using the LOOKUP formula using a lookup table from another help post, but it didn't work. I also tried a nested formula, but it didn't work either. Here's the post I'm referring to: https://community.smartsheet.com/discussion/i-want-utilise-if-then-function-smart-sheet

Find more posts tagged with

Human Resources

Construction

Formulas

IT & Operations

Project Management

Comments

Andreas S.

Hi Horace,

If your material list is not to long it´s maybe easiest to enter something like this into [cell2]:

=If([cell1]="4X8 Sheet";32; if([cell1]="4X12 Sheet";48; if(.....)))

(the "32" is the "then" for the first if, everything after 32 is the "else" for the first if)

Your [cell3] needs this:

=[cell2] * [cell4] / [cell5]

Hope this helps

Andreas

Andreas S.

just learned that non Germans (maybe not all?) have to use komma instead of semikolon, so if it doesn't work replace the Semikolon...

Horace

I had to try several things, but think I figured it out. A couple of corrections to the formulas you mention above. Would you please confirm that this is correct?

1. Cell name does not need to be in [ ] brackets

2. commas should replace the semi-colons in the above formula you mention...

Is that right?

Here's what I used that seems to work: =IF(Material1 = "4X8 Drywall", 32, IF(Material1 = "4X9 Drywall", 36), if(....)))

Thank you

Andreas S.

That comma / semicolon thing is a nightmare, I have to use semicolon, if yours is happy with comma then that's what has to be in your region.

The [ brackets should always be OK but not always needed.

You will need one If for each of your products.

Horace

Thank you!

Paul Newcome

How many different materials do you have? There are a few other ways this could be set up to provide more efficiency and flexibility if you have a longer list of materials. My general rule of thumb is 5 or more get converted into a table instead of a nested IF statement.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?