IF certain columns are not blank then autofill another column based on this?

Emmet McKenna

Hi, I want to autofill a column [Column 1] based on whether a number of other columns contain text/date, i.e.

IF [Column 2] is not blank then put '-' in [Column 1]

IF [Column 3] is not blank then put 'P1' in [Column 1] (this would override above)

IF [Column 2] is not blank then put 'P2' in [Column 1] (this would override above)

IF [Column 2] is not blank then put 'P3' in [Column 1] (this would override above)

The function can be contains text, a date or is not blank. It doesn't really matter. Would appreciate any help!

Andrée Starå

Hi @Emmet McKenna

Try something like this.

=IF([3]@row <> ""; "P3"; IF([2]@row <> ""; "P2"; IF([1]@row <> ""; "P1")))

The same version but with the below changes for convenience.

=IF([3]@row <> "", "P3", IF([2]@row <> "", "P2", IF([1]@row <> "", "P1")))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Emmet McKenna

Try something like this.

=IF([3]@row <> ""; "P3"; IF([2]@row <> ""; "P2"; IF([1]@row <> ""; "P1")))

The same version but with the below changes for convenience.

=IF([3]@row <> "", "P3", IF([2]@row <> "", "P2", IF([1]@row <> "", "P1")))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Emmet McKenna

Thanks Andrée, that worked perfectly!!!

Andrée Starå

@Emmet McKenna

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Khanambano

Hi,

I was wondering if you can assist me with a similar IF formula. I am trying to automate the status column for completed work based on "All Work Completed Date." The formula im trying to incorporate should say IF there is a date in the "all work completed column, then the status should change to complete.

=IF([All Work Complete Date@row] <>""; "Complete) is giving me unperishable. know how i can fix this?

Genevieve P.

Hi @Khanambano

It looks like you're just missing an end quote around the word "Complete" or "Completed"

=IF([All Work Complete Date@row] <>""; "Complete")

However, I would recommend using a Change Cell workflow instead of a formula in this instance! That way you can keep other manually entered values (such as "In Progress") in the column, but the automation will change it to "Complete" when a date is entered.

Here's more information: Change the Value of a Cell in an Automated Workflow

Cheers,

Genevieve

Khanambano

Hi @Genevieve P.

I tried to the automated workflow and i made a major mistake which changed the orginal data set that i had. I put in a customer support request with smartsheet to restore my previous sheet.

This is what my automation looks like:

However, when i turned this automation on, it changed all rows, even those that were blank to completed.

How can i fix this? your help is appreciated immensely as i just messed up my entire data set.

Khanambano

Hi @Genevieve P.

I tried to the automated workflow and i made a major mistake which changed the orginal data set that i had. I put in a customer support request with smartsheet to restore my previous sheet.

This is what my automation looks like:

However, when i turned this automation on, it changed all rows, even those that were blank to completed.

How can i fix this? your help is appreciated immensely as i just messed up my entire data set ='(

Genevieve P.

Hi @Khanambano,

What plan type are you on? If you're on a Business or Enterprise plan and you're either an Admin or Owner of the sheet, you can access the Activity Log in the sheet to download a Sheet Snapshot: Request a sheet snapshot

This will give you an excel file of the data so you can copy/paste in the correct Status back into the column.

In regards to your workflow, I would suggest adding in a Condition Block to make sure the status is only updated if the change to the cell is not-blank.

Let me know if that worked!

Cheers,

Genevieve