Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

✭✭✭✭
edited 10/17/22 in Formulas and Functions

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.

Tags:

Best Answer

  • ✭✭✭✭
    Answer ✓

    @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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6