If statement that copies text from 2 columns into one column
![Marla Berge](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
= 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.
Best Answers
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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.
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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.
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you, both! The formula works great and I'll try tackling the VLOOKUP/INDEX/MATCH one next!
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!