If and collect

Sarah Miller

Hello -- in an ideal world, I am trying to pull dates from an existing SmartSheet (all dates live in 1 cell) into another sheet where the main column has the dates listed vertically down and the top row shows each facilitator in a separate column. The intention is to pull their unavailable dates from the existing sheet into this new one where if they have marked they are unavailable on a certain date, the new sheet will show that for that facilitator, on that specific date, they are out.

When I started to play, I found that having the facilitator name in the top row was not allowing me to pull the data; when I moved the facilitator name to the main column (which is not how I want it), I was able to pull over ALL of the dates they are unavailable, but that is not the ideal format nor helpful because I need to view each date individually. Since I want each date to be it's own row (ideally) or column (if necessary) to then show out.

The formula that allowed me to successfully pull all of the dates over, with the facilitator in the main column was: =INDEX(COLLECT({Class Scheduling - Resources - Unavailable Dates}, {Class Scheduling - Facilitators}, Facilitator@row), 1).

However, like I noted, I really want the facilitators separated individually into columns, with the dates as the main column on the far left. Can I do this? And can a formula separate out the data where the cell originally has ALL UNAVAILABLE DATES together but I want the sheet to be able to say, "oh, this person's dates include July 5 that they cannot teach, I'll mark this as OUT".

Attaching what I would like the ideal setup to be for the sheet that will show the end information:

Here is where the data is coming from so you can see what I mean by all the dates in one cell:

Thanks in advance for your help!

sharkasits

@Sarah Miller In the source, how are the unavailable dates entered? Is that freeform text or is there a dropdown? I want to understand the format to help develop a formula for you.

Genevieve P.

Hi @Sarah Miller

You can use a CONTAINS function to see if a cell in your source sheet contains the date you have listed in the text date column.

Then if it does, you can return the Facilitator name to that Facilitator column.

However, it sounds like there may be multiple facilitators that could have selected the same date. In this case, I would use a JOIN(COLLECT function to look into your source sheet and bring back all possible facilitators that have selected that date:

=JOIN(COLLECT({Class Scheduling - Facilitators}, {Class Scheduling - Resources - Unavailable Dates}, CONTAINS(Date@row, @cell)), " / ")

Based on your screen capture, it looks like you have different locations for your facilitators. You can add that in as a criteria in your COLLECT function, like so:

=JOIN(COLLECT({Class Scheduling - Facilitators}, {Class Scheduling - Resources - Unavailable Dates}, CONTAINS(Date@row, @cell), {Class Scheduling - Facilitator Location}, CONTAINS("US", @cell)), " / ")

However there currently isn't a way to then parse that out into multiple columns. You would have one column with a group of facilitators. Does that make sense, and will it work for you?

Cheers,

Genevieve