Help with Formula - Look a value in a cell and if it is 1 of 4 values fill in a different cell

Frank S.

I'm importing data from a legacy excel sheet and the priority is designated with a 1-4 with a critical to low. I want to check that column and if it is one of the values it puts an update severity in a different column.

Since the new severity column may have values in it going forward do not want to overwrite an existing value.

Any help would be great.

Thanks,

Frank

Frank S.

I added the changes and it works great!

Thanks for the help

Paul Newcome

Are you able to provide some screenshots of what you currently have as well as some manually entered data that shows what you are trying to accomplish?

Frank S.

Here is a screen shot:

Screen Shot 2021-04-15 at 10.34.53 AM.png

I created a "helper" column to do they check, which I may not need.

If Priority is high & Severity is blank fill in Severity with option 2.

Let me know if this helps.

Paul Newcome

Will you also be manually updating the Severity column?

Frank S.

Yes, the severity column will be updated via a new form submission.

Paul Newcome

You are going to end up having to enter the formula every time then as the form submission will override the formula which will make auto-fill not work. Setting it as a column formula means the field can't be edited in the form.

I am going to suggest another column where it says that if the form entry column is not blank, the copy, but if it is blank then fill it in.

Does that sound like an option for you?

Frank S.

Sure I'm open to suggestions.

Paul Newcome

Ok. Insert a text/number or dropdown type column.

First we want to write the nested IF that will populate based on the Priority column. I am not sure exactly what options you have in that column, so I will just use the numbers 1 - 4. You will need to edit each of the text strings in the final formula to represent what you actually have in your sheet.

=IF(Priority@row = "1", "text for if 1", IF(Priority@row = 2, "text for if 2", IF(Priority@row = "3", "text for if 3", "text for if 4")))

Now we need to write an IF that says if the [Urgency/ Severity Level] is blank, run the above, otherwise copy the [Urgency/ Severity Level] cell.

=IF([Urgency/ Severity Level]@row = "", above_formula, [Urgency/ Severity Level]@row)

=IF([Urgency/ Severity Level]@row = "", IF(Priority@row = "1", "text for if 1", IF(Priority@row = 2, "text for if 2", IF(Priority@row = "3", "text for if 3", "text for if 4"))), [Urgency/ Severity Level]@row)

Frank S.

I added the changes and it works great!

Thanks for the help

Paul Newcome

Happy to help. 👍️