Reference date above to carry on sequence when row moved

David Clunie

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.

Find more posts tagged with

Formulas

Accepted answers

All comments

Genevieve P.

Hi @David Clunie

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

David Clunie

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

Genevieve P.

Hi @David Clunie

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.

Screen Shot 2022-06-14 at 10.13.15 AM.png

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.