Trying to create an on-call schedule with auto-calc'd dates

I am setting up an on-call schedule for all of the teams in the department. I want the start dates to auto-calc based on the prior row for the same team. I used AI to create a formula and it works.

But when I went to make it a column formula, SS says that the format is not correct!

This is the sheet. This is the formula in the Start Date column for row 3 :

=IF(Team@row = Team2, [Start Date]2 + 8, "")

Suggestions?

Also, I want to use this same sheet for all of our different teams, so what I really wanted to come up with was some type of formula or automation that finds the row with the most recent Start Date where the Team value is the same and then determine the next Start Date. That one stumped me.

Thanks!

Find more posts tagged with

Formulas

Accepted answers

Michelle Choate 2

Here is an excerpt from Smartsheet's help on Column Formulas.

Column formula limitations

Only formulas that do not reference specific row numbers can be converted to column formulas. Use the following table as a guide when writing column formulas:

Reference Type	Example	Supported?
@row	[Task Name]@row	Yes
Column references	[Task Name]:[Task Name]	Yes
Cross sheet references	{Task Name Column}	Yes
Cell references	[Task Name]1	X
Absolute references	[Task Name]$1	X
Partial-column ranges	[Task Name]1:[Task Name]12	X

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

All comments

Michelle Choate 2

Here is an excerpt from Smartsheet's help on Column Formulas.

Column formula limitations

Only formulas that do not reference specific row numbers can be converted to column formulas. Use the following table as a guide when writing column formulas:

Reference Type	Example	Supported?
@row	[Task Name]@row	Yes
Column references	[Task Name]:[Task Name]	Yes
Cross sheet references	{Task Name Column}	Yes
Cell references	[Task Name]1	X
Absolute references	[Task Name]$1	X
Partial-column ranges	[Task Name]1:[Task Name]12	X

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Cathy Salscheider

thank you Michelle

I didn't remember that there were rules around what can be converted to column formulas. Now I just need be more creative… I thought that this was going to be a breeze - LOL

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.