Date sequence based on row position

Campbell H
Campbell H ✭✭
edited 05/01/22 in Formulas and Functions

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?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!