Date sequence based on row position

Campbell H

I'm trying to provide a production schedule for our team to use where they can drag a job (row) up and down, and based on it's position it adjusts the schedule. The duration of the job in work days is coming from another sheet using index/match formula. I want the team to be able to add a job number which then looks up the duration of production days. Then depending on where it is dragged in the sheet, up or down, it chooses the start date as the finish date of the row directly above.

This function would almost work if we set up a Project sheet and manually updated the duration and predeccesor but that's too reliant on human input for my liking and you can't just drag a line up and down and have it resequence.

It also almost works if you set up the project on a non-project sheet and manually set the sequence ie start date is the row above end date, plus the duration of the production time. However dragging the row up and down mucks up the sequence and the formula needs to be redone and dragged down the page again.

If the @row-1 command worked that would be a simple fix to always use the value of the row above. However I can't seem to get that command to work.

Does anyone know of a solution?

Alan P.

Are you using any helper columns?

Campbell H

Thanks @Alan P. Yes I've added helper columns per @Paul Newcome solution here https://community.smartsheet.com/discussion/82131/is-there-a-way-to-reference-the-row-above

Which gives me the row number regardless of sorting up and down, but I am stuck on trying to use that to reference a date on the row above. I can get a single row to work using IF MATCH with the row helper column as the range, but when I drag the formula I get a circular reference.

Paul Newcome

You would need to use an INDEX function.

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

Campbell H

Thanks @Paul Newcome I have tried this and it works for a single row. Then when I add production days from start date to give me the end date I get a circular reference. Here's a quick video I did that shows the issue. https://watch.screencastify.com/v/kKGyyKRLlsmUYH8ESD5r

The goal is to give production team members the ability to slide rows up and down, and thus adjust the production sequence as each row is a production job. However always maintaining the flow that the row below gets it's start date from the row above's end date.

Paul Newcome

Ok. Put the original start date in a Sheet Summary field. This would be the very base start date that everything else hinges on.

Then in the Start Date column you would enter:

=WORKDAY([Original Start Date]@row, SUMIFS([Production Duration In Days]:[Production Duration In Days], ROW:ROW, @cell < ROW@row))

The duration column would be manual entry.

Finally the End Date formula would be:

=WORKDAY([Start Date]@row, [Production Duration In Days]@row)