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

Andrew McCrae · 05/22/23

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?

Andrew McCrae · 05/22/23

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å · 05/22/23

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!

Andrew McCrae · 05/22/23

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?

Andrew McCrae · 05/22/23