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

Options

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

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

    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)

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    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 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