Automation to "Change Cell Value" to embed a formula into the cell

Sarah_lee123

Trying to set an automation to "Change Cell Value"

2 columns involved: "BARCODE" and "PACKAGE UID/LOT #"

If "PACKAGE UID/LOT #" is not blank, then I don't want to override the data in the cell, however, in the absence of data in that cell ("PACKAGE UID/LOT #" is blank), then duplicate the data from "BARCODE" into "PACKAGE UID/LOT #"

I programed it to enter the formula =BARCODE@row into the blank cell, but when it populates, it adds an apostrophe to the beginning of the text so that the formula can't function once inside the cell. See how it comes up as text rather than use the formula?

Any other ideas on how I can get the cell contents to equal the BARCODE contents if the PACKAGE UID/LOT # is blank, but not if it already has pre-existing cell contents?

Thanks a ton as always!

Paul Newcome

You would need a third column with an IF statement in it.

=IF([PACKAGE UID/LOT #]@row <> "", [PACKAGE UID/LOT #]@row, BARCODE@row)

Sarah_lee123

Dang, ok, I see how this works, but I'd have to change my entire system and that will be a lot of trouble. I'll have to decide if that one is worth it to me. I really just need the formula to populate the "PACKAGE UID/LOT #" Column.

The way you recommended will populate the Helper Column, but that column is not linked throughout the multi sheet system like the "PACKAGE UID/LOT #" column is.

I'll play around and see if I can make it work for me, otherwise I'll just have to find another workaround.

Thanks for your time @Paul Newcome

Paul Newcome

Yes. It can definitely be a pain. That's one of those things that can really mess a solution up if you didn't know about it beforehand.

Sarah_lee123

@Paul Newcome

True, growing pains.

Thanks for all your help as I develop a more robust understanding of the Smartsheet system.

Paul Newcome

Always happy to help. 👍️

I will admit that there are some seemingly simple "catches" that can be quite a bit of a painful learning experience the first time you run into them, but those tend to be forgotten less frequently. Haha.

I will also admit that I keep a "black book" type of workspace of things I have learned away, crazier formulas and solutions, tips and tricks, etc. that I refer back to quite frequently.