Help with formula using weeknumber

Natalia Kataoka

Hi all. I think what I am looking for is a weeknumber formula, but I've having trouble figuring out how this will work.

I have a sheet that lists 31 "fundamentals" of our company. Each week we have a different fundamental to focus on and will be discussing said fundamental at the beginning of meetings. I have sheets for each meeting, with the meeting's date, and I'd like the fundamental to pull into each without having to look it up and manually enter it. The problem I am running into is I am unsure how to write the formula so that when we reach the 31st fundamental, it will start over at #1 on the following week. Also, since there are 52 weeks, but I only have 31 fundamentals I cannot just list each out with a week number on my source sheet and use that as a reference in my meeting sheets.

I have columns for the fundamental #, fundamental name, week number, start date, and end date. I do not know which of these I even require!

Ideally, I'd also like to *not* have to add rows so that start/end dates perpetually continue or update the date ranges to coincide with each new year.

I'd like the info to pull here:

The Format and Priority columns are Text/Number, that could be used if needed.

Any ideas appreciated!

Mark Cronk

Hi @Natalia Kataoka,

Have you tried using =WEEKNUMBER(today()) to return a number from 1-52 representing the week of the year? Move your weekender column to the far left of the data range and use weeknumber with a vlookup to find the fundamental topic for that week. To make it perpetual, combine it with the YEAR() function to lookup 20201 for week 1 in year 2020. Insteak of week number use year and week in the functions table. Build your function table to loop through the topics for a few years out.

Help?

Mark