Reference date above to carry on sequence when row moved
Hi
Sorry to go into an old post but this is what I want, to look at the dates.
I want to look at moving rows which have sequential dates based on planning.
=WORKDAY([Online Date]248, 1, {Holidays Range 2})
I also want this to inherit from the date above to repopulate the sequence in order as shown
Currently, if we move a row we have to rebuild and pull down all online date formulas to rectify.
Post I found but couldn't get this to work.
Insert an auto-number column (called "Auto) that has no special formatting.
Next insert a text/number column (called "Row") with the following column formula:
=MATCH(Auto@row, Auto:Auto, 0)
Then you can incorporate this into your formula
Row@row - 1 will always give the row above regardless of sorting, deleting, or adding rows.
Answers
-
If you're placing the formula in the same column you're referencing (Online Date) then you'll receive a circular reference error if you try to use an INDEX formula to check the entire column and return the date from the previous cell.
Instead, I would suggest using this helper ROW column that you inserted as the number you want to add to the WORKDAY formula, locking the formula to the very top cell in the sheet which was populated with the first date manually.
That would look something like this:
=WORKDAY([Online Date]$1, Row@row, {Holidays Range 2})
The "Row@row" adds the number of working days to your Online Date in the first cell. This means if you're on row 5, the formula would output:
=WORKDAY([Online Date]$1, 5, {Holidays Range 2})
Let me know if this makes sense and will work for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi Genevieve
Thanks for your assistance its greatly appreciated.
Sorry, I can not get this to work, either I get a circular reference or blocked.
In essence, I want to be able to move up any row and it takes the next date. So the one shown below when moved would read 22/06/22 once moved.
In xls, they can use the INDIRECT function but not so easy in smart sheets. I have made a test smartsheet to try out these formulas
-
Can you post a screen capture that shows the formula in your sheet, and the errors?
Keep in mind that the very first cell would need to be manually populated, the formula would only be starting from row 2 down.
Notice that the Auto column in my example has different numbers than the Row column. That's because the rows were moved around, but the Row column adjusts to make sure the order is correct. Then since the formula uses that Row column as the day to add into the workday, it adjusts as rows move around, too!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!