Automation Single select - Change cell value

Kimbh

Hi, I have single select with Group A, Group B and Group C. The goal is that when the person filling out the form picking one of these that one of these groups that on the sheet the price per unit fills out.

If in an automation can I set it up so when a new row is added, depending on what single select group was picked, the cell value in the price per unit fill out is the correct price.

I can't find a way for this to happen. Is it even possible.

Thank you

Jeff Reisman

You can do this more easily with a formula vs automation.

In your Price Per Unit column (change column names, values to fit your sheet:)

=IF([Group]@row = "Group A", 1.50, IF([Group]@row = "Group B", 2.25, IF([Group]@row = "Group C", 3.75, "")))

This formula uses Nested IFs. The basic IF formula syntax is:

IF(logical expression is true, value if true, value if false)

IF Function | Smartsheet Learning Center

We can nest these, so that the value if false is another IF. The formula above says in English:

If the Group column is Group A, put a 1.50 value in this column; if it's not, then consider if the Group is Group B, and if it is Group B, set the value to 2.25; if it's not, consider if it's Group C, and if so, set the value to 3.75; If it's any other value, leave this column blank (empty quotes, "").

Jeff Reisman

You can do this more easily with a formula vs automation.

In your Price Per Unit column (change column names, values to fit your sheet:)

=IF([Group]@row = "Group A", 1.50, IF([Group]@row = "Group B", 2.25, IF([Group]@row = "Group C", 3.75, "")))

This formula uses Nested IFs. The basic IF formula syntax is:

IF(logical expression is true, value if true, value if false)

IF Function | Smartsheet Learning Center

We can nest these, so that the value if false is another IF. The formula above says in English:

If the Group column is Group A, put a 1.50 value in this column; if it's not, then consider if the Group is Group B, and if it is Group B, set the value to 2.25; if it's not, consider if it's Group C, and if so, set the value to 3.75; If it's any other value, leave this column blank (empty quotes, "").

Kimbh

Thank you, works perfectly.

Kimbh

Hi, I'm using

=IF([Group]@row ="Unit 1", 200, IF([Group]@row = "Unit 2", 300, IF([Group]@row = "Blank", "", ""))) with Group being the single select field and putting the price in the PPU field.

I want a single select option "blank" that will clear the function codes so a custom price can be added. Is that possible?

Jeff Reisman

@Kimbh

You can't manually place values in a field that ordinarily contains a formula, without overwriting that formula. The way to do this would be to have a separate column for adding a custom price. Then, if the Group selection is "Blank", we reference the value in the custom price field.

So let's call that column "Custom Price"

=IF([Group]@row ="Unit 1", 200, IF([Group]@row = "Unit 2", 300, IF([Group]@row = "Blank", [Custom Price]@row, "")))

Kimbh

@Jeff Reisman I came up with

=IF([Group]@row = "Unit 1", 200, IF([Group]@row = "Unit 2", 300, IF([Group]@row, = "Custom", [Custom Price]@row, "")))

It says Incorrect Argument set.

--- --- ---

=IF([Group]@row ="Unit 1", 200, IF([Group]@row = "Unit 2", 300, IF([Group]@row = "Custom", [Custom Price]@row, "600")))

It doesn't send it to another column. Works in the, but keep the price in the same column as the function codes.

--- --- ---

I decided to try for something that put all the prices in another column beside the one the function codes is in.

=IF([Group]@row ="Unit 1", [Custom Price]@row, "200", IF([Group]@row = "Unit 2", [Custom Price]@row, "300" IF([Group]@row = "Custom", [Custom Price]@row, "")))

Thank you so much for the help

Jeff Reisman

This is a good approach, but having your price per unit values in quotes will make them text values, and it's hard to calculate a cost from a text value. Leave the quotes off if you want to perform calculations with the 200 and 300 values.