How to Add an absolute reference AND have a column formula without cross sheet references

Andrew McCrae
Andrew McCrae ✭
inSmartsheet Basics

Hi

image.png

Starting at "Part 1 Calc column Formulae are

=DATE([Year of next Event Adjusted for New Year]1, [Month of Next Event Adjusted if >12]1, 1)

=(NETDAYS([Reference date of a Monday (Used for Calc 2)]@row, [First Day of "Month of Next Event" Part 1 of calc]@row) - (7 * INT(NETDAYS([Reference date of a Monday (Used for Calc 2)]@row, [First Day of "Month of Next Event" Part 1 of calc]@row) / 7)) + 1)

=IF([Weekday of 1st Day of Month Part 2 of calc]1 = 1, [First Day of "Month of Next Event" Part 1 of calc]1 + 1, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 2, [First Day of "Month of Next Event" Part 1 of calc]1 + 0, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 3, [First Day of "Month of Next Event" Part 1 of calc]1 + 6, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 4, [First Day of "Month of Next Event" Part 1 of calc]1 + 5, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 5, [First Day of "Month of Next Event" Part 1 of calc]1 + 4, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 6, [First Day of "Month of Next Event" Part 1 of calc]1 + 3, IF([Weekday of 1st Day of Month Part 2 of calc]1 = 7, [First Day of "Month of Next Event" Part 1 of calc]1 + 2)))))))

=([First Monday of Month Part 3 of calc]1 + [Weekday of recurrence. Sunday = Day 1]1 - 2)

=IF(DAY([First Actual Weekday in Month Part 4 of calc]1) > 7, [First Actual Weekday in Month Part 4 of calc]1 - 7, [First Actual Weekday in Month Part 4 of calc]1)

=IF([Week of Recurrence in Month]@row = 0, [First Day of "Month of Next Event" Part 1 of calc]@row, [Day Corrected if beyond 7 Part 5 of Calc]1 + (([Week of Recurrence in Month]1 - 1) * 7))

=[Actual Next Event Date Part 6 of calc]@row - TODAY()

The above sheet calculates for each row an event date and emails an owner (in a column not seen here) when due. It worked perfectly when I had the reference positioned in one of the other columns but this would have necessitated cells being dragged down when a new row is added.

As a result I repositioned the reference and I have converted most formulae to column formulae but am getting syntax errors when trying to refer to the first cell in the right hand column. I use this cell to figure out what day of the week it is in the 9th column to the left. I want to avoid using cross sheet references as it will confuse the users. (I wont be around to explain) I looked at using a workflow to add the fixed date into every row but that isn't allowed either.

What is allowed that might help. please?

· Share on Facebook Share on Twitter

Best Answers

  • Andrew McCrae
    Andrew McCrae ✭
    edited 05/22/23 Answer ✓

    Got it to work. Sometimes in formulating a question, you are forced to think. I changed the formula to eliminate the reference by putting a fixed date like follows. I am sure I could make it more elegant but it is working for me it appears.

    =(NETDAYS(DATE(1900, 1, 1), [First Day of "Month of Next Event" Part 1 of calc]@row) - (7 * INT(NETDAYS(DATE(1900, 1, 1), [First Day of "Month of Next Event" Part 1 of calc]@row) / 7)) + 1)

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi@Andrew McCrae

    I hope you're well and safe!

    Excellent! Glad you got it working!

    Be safe, and have a fantastic day!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    ✅Please support the Community by marking your postwiththe accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Answers

  • Andrew McCrae
    Andrew McCrae ✭

    Another thought is Can I add a date into the formula directly instead of a reference as it is the same date for every row?

    · Share on Facebook Share on Twitter
  • Andrew McCrae
    Andrew McCrae ✭
    edited 05/22/23 Answer ✓

    Got it to work. Sometimes in formulating a question, you are forced to think. I changed the formula to eliminate the reference by putting a fixed date like follows. I am sure I could make it more elegant but it is working for me it appears.

    =(NETDAYS(DATE(1900, 1, 1), [First Day of "Month of Next Event" Part 1 of calc]@row) - (7 * INT(NETDAYS(DATE(1900, 1, 1), [First Day of "Month of Next Event" Part 1 of calc]@row) / 7)) + 1)

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi@Andrew McCrae

    I hope you're well and safe!

    Excellent! Glad you got it working!

    Be safe, and have a fantastic day!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    ✅Please support the Community by marking your postwiththe accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Categories

How exactly is your Data Shuttle set up? If it is set to replace everything in the sheet when it runs, it is deleting the cell being referenced by the widget and then putting new data in new cells.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":292,"urlcode":"Dashboards","name":"Dashboards"},{"tagID":474,"urlcode":"data-shuttle","name":"Data Shuttle"}]},{"discussionID":108213,"type":"question","name":"How to spread costs evenly across months, if they fall within those dates","excerpt":"I have various financial line items that span different dates. I'd like the costs to be spread per month automatically based on the date and cost entry. I've tried various functions already, IF, AND, and it says Unpearsable. I'd like to spread out the costs in the Cost column to the months on the right, based on the start…","snippet":"I have various financial line items that span different dates. I'd like the costs to be spread per month automatically based on the date and cost entry. I've tried various…","categoryID":321,"dateInserted":"2023-07-27T19:03:42+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T21:12:20+00:00","insertUserID":164209,"insertUser":{"userID":164209,"name":"AshwiniBiotech2023","title":"Sr. Director, Project Management","url":"https:\/\/community.smartsheet.com\/profile\/AshwiniBiotech2023","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T23:10:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164209,"lastUser":{"userID":164209,"name":"AshwiniBiotech2023","title":"Sr. Director, Project Management","url":"https:\/\/community.smartsheet.com\/profile\/AshwiniBiotech2023","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T23:10:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":14,"countViews":67,"score":null,"hot":3381071762,"url":"https:\/\/community.smartsheet.com\/discussion\/108213\/how-to-spread-costs-evenly-across-months-if-they-fall-within-those-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108213\/how-to-spread-costs-evenly-across-months-if-they-fall-within-those-dates","format":"Rich","lastPost":{"discussionID":108213,"commentID":387925,"name":"Re: How to spread costs evenly across months, if they fall within those dates","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387925#Comment_387925","dateInserted":"2023-07-28T21:12:20+00:00","insertUserID":164209,"insertUser":{"userID":164209,"name":"AshwiniBiotech2023","title":"Sr. Director, Project Management","url":"https:\/\/community.smartsheet.com\/profile\/AshwiniBiotech2023","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T23:10:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/IBAVVE3YLO8D\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T09:38:32+00:00","dateAnswered":"2023-07-27T19:21:12+00:00","acceptedAnswers":[{"commentID":387646,"body":"

You would use the below for Jan 2023 and adjust the month and year numbers accordingly for each of the other months.<\/p>

=IF(AND(MONTH([Start Date]@row)<= 1, YEAR([Start Date]@row)<= 2023, MONTH([End Date]@row)>= 1, YEAR([End Date]@row)>= 2023), [$ per month]@row)<\/p>"},{"commentID":387901,"body":"

Lets try a different approach.<\/p>

=IF(AND(VALUE(YEAR([Start Date]@row) + IF(MONTH(Start Date]@row)< 10, \"0\", \"//www.santa-greenland.com/community/discussion/105523/\") + MONTH([Start Date]@row))<= 202307<\/strong>, VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row)< 10, \"0\", \"//www.santa-greenland.com/community/discussion/105523/\") + MONTH([End Date]@row))>= 202307<\/strong>), [$ per month]@row)<\/p>


<\/p>

Basically we are creating a yyyymm stamp from the start and end dates and comparing them to the yyyymm stamp for that year\/month combo. The above is for July 2023 (202307).<\/p>"},{"commentID":387906,"body":"

@AshwiniBiotech2023<\/a> Check out my last comment.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108239,"type":"question","name":"Is an automated export of a Smartsheet to an Excel file possible?","excerpt":"Hi all, I have been having the most frustrating conversation with Smartsheet support on this topic and not getting answers to the question that I asked. I would like to set up an automation that will email me an Excel copy of my project plans on a weekly basis (or when triggered with a change - I don't really care) as I…","snippet":"Hi all, I have been having the most frustrating conversation with Smartsheet support on this topic and not getting answers to the question that I asked. I would like to set up an…","categoryID":321,"dateInserted":"2023-07-28T02:36:12+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T06:42:13+00:00","insertUserID":164243,"insertUser":{"userID":164243,"name":"Candace G","title":"Senior Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Candace%20G","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T06:50:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":137040,"lastUser":{"userID":137040,"name":"Gia Thinh","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Gia%20Thinh","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pXMt6Y5Kqjs!ZHDtmZp0Yms!Dwma-t47hzh","dateLastActive":"2023-07-28T23:47:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":45,"score":null,"hot":3381040105,"url":"https:\/\/community.smartsheet.com\/discussion\/108239\/is-an-automated-export-of-a-smartsheet-to-an-excel-file-possible","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108239\/is-an-automated-export-of-a-smartsheet-to-an-excel-file-possible","format":"Rich","tagIDs":[268,334,440,445,510],"lastPost":{"discussionID":108239,"commentID":387741,"name":"Re: Is an automated export of a Smartsheet to an Excel file possible?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387741#Comment_387741","dateInserted":"2023-07-28T06:42:13+00:00","insertUserID":137040,"insertUser":{"userID":137040,"name":"Gia Thinh","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Gia%20Thinh","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pXMt6Y5Kqjs!ZHDtmZp0Yms!Dwma-t47hzh","dateLastActive":"2023-07-28T23:47:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T06:32:42+00:00","dateAnswered":"2023-07-28T05:49:42+00:00","acceptedAnswers":[{"commentID":387737,"body":"

Hi Candace,<\/p>

The answer is YES. You can use \"Send as Attachment\" feature in the File menu of your sheet to setup the schedule for your need. You should be a licensed user to use this feature as well (I remembered that). <\/p>

Hope this works for you.<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":268,"urlcode":"Attachments","name":"Attachments"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"},{"tagID":510,"urlcode":"email-notifications","name":"Email Notifications"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":4892,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

趋势在智慧heet Basics

  • Dashboard Metric Breaks on Update of DataShuttle

    I am sure there is something I am missing. But I try to set a Dashboard Matrix on a field that is refreshed by Data Shuttle. But when the Grid refreshes it breaks the Metric and says Data missing even though there is data in the field. Is there anyway around this or does Data Shuttle always break a Metric Cell?

  • How to spread costs evenly across months, if they fall within those dates

    I have various financial line items that span different dates. I'd like the costs to be spread per month automatically based on the date and cost entry. I've tried various functions already, IF, AND, and it says Unpearsable. I'd like to spread out the costs in the Cost column to the months on the right, based on the start…

  • Is an automated export of a Smartsheet to an Excel file possible?

    Hi all, I have been having the most frustrating conversation with Smartsheet support on this topic and not getting answers to the question that I asked. I would like to set up an automation that will email me an Excel copy of my project plans on a weekly basis (or when triggered with a change - I don't really care) as I…

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video