Reference date above to carry on sequence when row moved

EOCLM

I have a formula that calculates the end date and I want the start date to equal the end date of the previous project. How do I have a cell reference a date from the previous row when the row is moved further down the list? I can only get it to reference one cell and then I have to redo the formula for the start date for the whole column.

Paul Newcome

You would first insert an auto-number column (formatting doesn't matter and called "Auto" in this example). Then you would insert a text/number column (called "Row" in this example) and enter the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then to pull in the date you would use:

=INDEX([End Date]:[End Date], Row@row - 1)

EOCLM

Thank you for the response. I added the "Auto" and "Row" columns with the INDEX formula but now I'm getting a circular reference error.

EOCLM

Thank you for the response. I added the "Auto" and "Row" columns with the INDEX formula but now I'm getting a circular reference error.

EOCLM

Thank you for the response. I added the "Auto" and "Row" columns with the INDEX formula but now I'm getting a circular reference error.