If statement that copies text from 2 columns into one column

Marla Berge

= IF([Revision]@row = " ",(IF([Design Change Order Requester]@row = " ", " ", (REPLACE([Design Change Order Requester]@row,20,20," requested" +[Design Change Order Comments]@row))))

What I want:

If Revision is blank, then check to see if Design Change order requester is blank. If yes, then Design Change Order Requester and Comments (1) stays blank, if Design Change Order Requester is not blank, then copy information in Design Change Order Requester and Design Change Order Comments into the Design Change Order Requester and Comments (1) field.

Once the Design Changer Order Requester and Comments (1) has been populated, I don't want the information to change even if Revision, Design Change Order Requester or Design Change Order Comments change.

Genevieve P.

Hi Maria,

Try this:

=IF(AND([Revision]@row = "", [Design Change Order Requester]@row = ""), "", IF([Revision]@row = "", [Design Change Order Requester]@row + [Design Change Order Comments]@row))

You may want to add in some sort of spacing between the two cells you're bringing together... like a comma or dash:

[Design Change Order Requester]@row + " , " + [Design Change Order Comments]@row

This formula will stay live though, which means that if any of those cells update, it will continually update the output of the formula. There currently isn't a way to lock in past history like this into the grid with a formula (please submit an Enhancement Request when you have a moment!).

That said, you can always use Cell History to see any past formula outputs. Hope that helps!

Cheers,

Genevieve

Andrée Starå

Hi @Marla Berge

To add to Genevieve’s excellent advice.

I developed a solution where it’s possible to store/lock information.

Have a look at my post here.

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would 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!

Genevieve P.

Hi Maria,

Try this:

=IF(AND([Revision]@row = "", [Design Change Order Requester]@row = ""), "", IF([Revision]@row = "", [Design Change Order Requester]@row + [Design Change Order Comments]@row))

You may want to add in some sort of spacing between the two cells you're bringing together... like a comma or dash:

[Design Change Order Requester]@row + " , " + [Design Change Order Comments]@row

This formula will stay live though, which means that if any of those cells update, it will continually update the output of the formula. There currently isn't a way to lock in past history like this into the grid with a formula (please submit an Enhancement Request when you have a moment!).

That said, you can always use Cell History to see any past formula outputs. Hope that helps!

Cheers,

Genevieve

Andrée Starå

Hi @Marla Berge

To add to Genevieve’s excellent advice.

I developed a solution where it’s possible to store/lock information.

Have a look at my post here.

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would 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!

Marla Berge

Thank you, both! The formula works great and I'll try tackling the VLOOKUP/INDEX/MATCH one next!

Andrée Starå

@Marla Berge

Excellent!

You're more than welcome!