LAST WEEK EVENTS FORMULA (INCLUDE DIFERENT YEARS)

[Manager_DGP]

[SUMMARY]

This topic is in two parts

1. Show Event Names By Type On A Weekly Calendar
2. Automatically Remove Rows Based On Today’s Date

Show Event Names By Type On A Weekly Calendar
I have a table called Events with an Event Name, Event Date and Event Type (Holiday, Client, Celebration, Conference)

I have another table called Calendar where I show the current week number. This helps our team stay focused on what’s upcoming especially when we may have a Conference, Client Event or Celebration to plan for.

SCENARIO: How do I related Table(Events) as a lookup field or formula to Table(Calendar) so that the Event Name will show up in the proper week?

By the way, thanks to @Bryce_Schmidt, @drassi and @Jeremy_Oglesby for solving the problem to ISO week numbers.

[JonathanBowen]

Hi @Manager_DGP - the short answer is “you can’t” - unlike, say, Excel, Airtable won’t do a lookup between two unrelated tables so you have to make the explicit link between the two to get this to happen. Here’s a couple of options:

Here’s your Events table:

You could have a “fully explicit” calendar, i.e. each day of the year and link the event to the day in the calendar:

You could group this by week to get more of a week view:

Or, have as “week starting” calendar, as you have in your post and link the event to the week:

The advantage of the first option is that when you make the link you’re explicitly matching the same date in each table; with the second, you’re matching a date with the week it falls into - not exactly hard, but possible more prone to error.

Hope this helps

JB