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