Convert formula to column formula without losing existing cell data

Darren Longnecker

Hello,

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

Thanks,

Darren

Leibel S

You can manually work through those that already have dates in them and manually copy them to the other sheet...

Darren Longnecker

@Leibel S This formula is on Sheet 1. I want to keep the dates on Sheet 1, in the same column as the formula. The goal here is to have the Approved Date change when the Status is set to "Aproved", which I have achieved with the copy/row trigger and INDEX/MATCH formula. This issue is retaining the data already in the column cells.

Leibel S

Your current rows automatically copy to sheet 2. What I am saying is that all previous rows you should manually copy them to sheet 2 with the correct date.

Andrée Starå

Hi @Darren Longnecker

As a possible workaround, you could add the helper column to the other sheet and reference the helper in the formula if it's not empty.

Make sense?

Would that work/help?

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!