Use a reference calendar sheet as a look up table to return values to a resource planning sheet

MPMurphy

Hello,

I have a resource planning sheet (Controls Resource Planning) that lists task for individuals along with other pertinent information. I have a Start Date and End Date column. I also have Period Start and Period Finish columns. Our company fiscal calendar consists of thirteen, four-week periods. Since I don't have the calendar app to make a custom calendar, I imported a csv file made for Outlook into a second sheet called Outlook Calendar 2022. I want to use the calendar sheet as a lookup table to return the value in the Subject column and place it in the Period Start column of the ELZ Controls Resource Planning sheet.

Basically, in words my formula/function needs to say:

IF {Outlook Calendar 2022} [Start Date] >= {ELZ Controls Resource Planning} [Start Date] <= {Outlook Calendar 2022} [End Date],

THEN move contents of {Outlook Calendar 2022}Subject to {ELZ Controls Resource Planning} [Period Start]

I don't know whether or not an actual IF statement with a nested AND function will work, or if I need to try and use VLOOKUP in combination with another function. Any suggestions would be appreciated.

Genevieve P.

Hi @MPMurphy

We can use an INDEX(COLLECT formula to say almost exactly what you have written out as your statement.

=INDEX(COLLECT({Week Start}, {Week Start}, @cell <= [Start Date]@row, {Week End}, @cell >= [Start Date]@row), 1)

The first thing to list in the COLLECT Function is the range/column you want to pull data back from, so in your case the Start Date in your Outlook Calendar.

Then you list each column with the criteria you're looking for.

Let me know if this makes sense and works for you!

Cheers,

Genevieve

MPMurphy

Thanks Genevieve. I will give this a shot and see what I come up with.

MPMurphy

@Genevieve P.

I think I'm still a little confused on what data should correspond to each argument of the COLLECT functgion.

Below is what I tried and it gives me the "INCORRECT ARGUMENT SET" error.

=INDEX(COLLECT({Outlook Calendar 2022 Range 1}, {Outlook Calendar 2022 Range 2}, [Start Date]@row >= {Outlook Calendar 2022 Range 3}, {Outlook Calendar 2022 Range 6}, [Start Date]@row <= {Outlook Calendar 2022 Range 6}))

For example, I want the value "P1 W1 - week 1" to show up in {ELZ Controls Resource Planning} [Period Start] cell when {ELZ Controls Resource Planning} [Start Date] fall between {Outlook Calendar 2022}[Start Date] AND {Outlook Calendar 2022}[End Date].

I would repeat the same function to populate the {ELZ Controls Resource Planning} [Period Finish] cell as well. The result should tell me which period the task will start in, and which period the task will end in based on looking up in the calendar sheet.

Genevieve P.

Hi @MPMurphy

It looks like you're selecting multiple columns/ranges, but you only need to reference three columns: the Start Date, the End Date, and the Subject columns in your Outlook Calendar.

I misunderstood and thought you wanted the Date of the period start to appear in your Period Start date column, but it sounds like this is a Text Number column and you want the Subject instead.

This is the structure of that formula:

=INDEX(COLLECT({Subject Column}, {Week Start}, @cell <= [Start Date]@row, {Week End}, @cell >= [Start Date]@row), 1)

The first column you list in the Collect function is what you want to bring back:

{Subject Column}

The second column you list is what you're looking in to, in this case the Start Date column in the Outlook sheet:

{Week Start}

Then you list the Criteria you're looking for in this column. In this case, we're checking each cell (@cell) of the previously stated range for the Start Date in your CURRENT sheet, making sure that the Start Date in Outlook is either less than or equal to the Start Date in this current sheet.

@cell <= [Start Date]@row

But then we need a second criteria to make sure that we're checking when that week ENDS as well as when it starts. To do so, we list the Week End column in your Outlook sheet:

{Week End}

Then we list the criteria that we're looking for in the Week End column, in each cell (@cell), and we're checking to see if the Week End date for that same row with the Start Date in Outlook is greater than or equal to the Start Date in this current sheet:

@cell >= [Start Date]@row

=INDEX(COLLECT({Subject Column}, {Week Start}, @cell <= [Start Date]@row, {Week End}, @cell >= [Start Date]@row), 1)

Does that make more sense? Then for the End Week Subject we just change out the criteria we're looking for in these same ranges:

=INDEX(COLLECT({Subject Column}, {Week Start}, @cell <= [End Date]@row, {Week End}, @cell >= [End Date]@row), 1)

Cheers,

Genevieve