How to Add an absolute reference AND have a column formula without cross sheet references
Hi
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?
Best Answers
-
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)
-
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 post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
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?
-
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)
-
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 post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives