Formula for Join, Collect, and If Statement - Resources Availability

Sarah123

I have a sheet that contains my resources' availability for each week and I'd like to pull in a list of those that have availability into another sheet so we know who to assigned to tasks on a project. I figured out the Join(Collect) part of the statement but I need to get the IF statement to work. Please see below for how the sheets are setup:

Resource Availability Sheet:

I have a column with the list of the Resources and what role they have and then I have a column for each Fiscal Year/Week that contains the number of hours they are available.

Availability Sheet:

I have columns pulling in the tasks that are unassigned along with their start/end dates and number of hours per week and the FY Ws for the start/end. I'd like to get a formula to be able to look at the function/role in this sheet and see when the FY W start/end (can be different columns) and see who is available during that week(s) from the Resource Availability Sheet (it'd be perfect if I could have the formula look at the hours a week per task and see if the resource has that many hours or more available in that week).

The current formula I have is =JOIN(COLLECT({Resources}, {Function}, Function@row), ", ") . That works but it's not looking at the FY W in the Resource Availability Sheet - I'm not sure where to put the IF statements.

Sarah123

@Paul Newcome I think I got it! =JOIN(COLLECT({Resources}, {Collect Info}, (CONTAINS([FY W Start]@row, @cell)), {Function}, Function@row), ", ") . I'm not sure if there is any major difference to using contains instead of has but I did manage to get it to work with the contains instead.

Darren Mullen

@Sarah123 I don't think you'll need an IF statement. Collect can take multiple range / range criterion pairs, so simply add the FY W range and criterion to you collect statement.

Does that answer your question?

Sarah123

Unfortunately when I try that I get #Incorrect Argument Set. The formula I used is =JOIN(COLLECT({Resources}, {Function}, Function@row, {FY W Availability}, [FY W Start]@row), ", ") . I even tried to put an @cell>0 so that it only returns those resources who have availability during that week but no luck either.

Sarah123

Update: I can get this to work if I make a reference to each column in the Resource Availability Sheet and then manually select that reference depending on what is in the FY W Start column on the Availability Sheet (example: =JOIN(COLLECT({Resources}, {Function}, Function@row, {FY23W1}, @cell > 0), ", ") ). But this isn't feasible as I want this to be a formula that is automatically pulling the data and not someone manually entering in the information. Is there a way to use the find formula to find the data that's in the FY W Start cell in the Availability Sheet and then create a reference with the column that it's found in the Resource Availability Sheet so I can use that? Or even to have a way to take the data in the FY W Start cell in the Availability Sheet and have that act as a reference to a column in the Resource Availability Sheet?

Paul Newcome

You can use an IF statement to output a cross sheet reference, but it looks as if you would end up with a very long nested IF. I am sure there is another way, but I can't quite think of it just yet.

=JOIN(COLLECT({Resources}, {Function}, Function@row, IF([FY W Start]@row = "FY23W1", {FY23W1}, IF([FY W Start]@row = "FY23W2", {FY23W2}, IF([FY W Start]@row = "FY23W3", {FY23W3}, IF([FY W Start]@row = "FY23W4", {FY23W4})))), @cell > 0), ", ")

Sarah123

Thanks Paul! Yeah I was hoping to have an easier way to reference the Resource Availability Sheet. In the meantime, this will kinda work (some projects go out a couple years so referencing that many weeks would be crazy lol). If you can think of a better way please let me know. 😀

Paul Newcome

Really the part that makes it so tricky is that you are joining multiple rows together instead of just indexing a single row. I'll keep thinking on it.

@Andrée Starå @Genevieve P. @Kelly Moore Any thoughts on this one?

Paul Newcome

Try this:

Insert a helper column on the source sheet (multi-select dropdown) and this formula:

=JOIN(COLLECT([FY22 W46]$1:[FY23 W19]$1, [FY22 W46]@row:[FY23 W19]@row, @cell> 0), CHAR(10))

This will collect all of the weeks each resource is available. Because of the absolute references in the first range, you will need to dragfill the formula down as opposed to creating a column formula.

Then in the target sheet you would reference this helper column with a HAS function.

=JOIN(COLLECT({Resources}, {Helper Column}, HAS(@cell, [FY W Start]@row), .............................), ", ")

Is this getting us closer? 🤞

Sarah123

@Paul Newcome This is very close I think! What would be in the ... part? Is that a reference to each column in the source sheet?

I have =JOIN(COLLECT({Resources}, {Function}, Function@row, {Collect Info}, HAS(@cell, [FY W Start]@row, ", "))) in the sheet for the formula but I receive a blank response.

Sarah123

@Paul Newcome I think I got it! =JOIN(COLLECT({Resources}, {Collect Info}, (CONTAINS([FY W Start]@row, @cell)), {Function}, Function@row), ", ") . I'm not sure if there is any major difference to using contains instead of has but I did manage to get it to work with the contains instead.

Paul Newcome

@Sarah123 The ............ part is the rest of your JOIN/COLLECT. I thought you were including some other criteria such as role.

The reason for the multi-select column and the HAS function is because CONTAINS will pick up on

FY23 W1 if you are searching for FY23 W10

It adds another layer of filtering to ensure you really are only grabbing exactly what you want.

Sarah123

Ahhhh yeah that makes sense. And if I try to put 01 or 02 as the week then it won't pick it up. Is there a reason why the HAS function isn't working?

Paul Newcome

I'm not sure. I used it in a test sheet successfully. In the collection column on the source sheet... Do you have it set as a multi-select dropdown, and did you use CHAR(10) as the delimiter?

Sarah123

I don't have anything set as a dropdown in that sheet. Which field should be a dropdown?

Paul Newcome

The one where each of the different available weeks are listed. The helper column where we JOIN/COLLECT the first row based on the availability "@row".

This bit:

"Insert a helper column on the source sheet (multi-select dropdown) and this formula:

=JOIN(COLLECT([FY22 W46]$1:[FY23 W19]$1, [FY22 W46]@row:[FY23 W19]@row, @cell> 0), CHAR(10))

This will collect all of the weeks each resource is available. Because of the absolute references in the first range, you will need to dragfill the formula down as opposed to creating a column formula."