Convert formula to column formula without losing existing cell data


I have a sheet where I'm trying to add a formula as a column formula so that it applies to new rows, the issue is many cells in the column already have data (Date column) that I don't want to lose. The formula is an INDEX/MATCH that is referencing another sheet to obtain a modified Date. I have a trigger to copy a row when the status is changed to obtain the modified date in Sheet 2

Sheet 1 Columns

Work Order Assigned Date Status

Sheet 2 Columns

Work Order Modified Date

The formula I am using is:

=(IFERROR(INDEX({Sheet2 Modified Date}, MATCH([Work Order]@row, {Sheet2 Work Order}, 0)), ""))

I tried an IF function to check to see if the column was blank and received a "Circular Reference" error.

=(IF([Assigned Date]@row = "", (IFERROR(INDEX({Sheet2 Modified Date}, MATCH([Work Order]@row, {Sheet2 Work Order}, 0)), "")), [Assigned Date]@row))

I have a feeling I will need to create a helper column, but that will clutter my already 40 column Sheet 1 and I still don't know how I would get the original Date into the Assigned Date column.

Any ideas are appreciated




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!