Join Collect Function

Vanessa Tanner

Hello community, I am trying to collect all dates into one field when a presenters name is present.

Sheet one has my presentations scheduled for 2024-2025 - (need dates pulled into sheet three)

Sheet two has me presentations schedule for 2023-2024 - (need dates pulled into sheet three)

Sheet three has all presenters I have access to. I am trying to see who I can tap for assistance that does not have upcoming presentations or has not presented in a while.

My presenters are a drop down I am not sure if that makes a difference of not.

My first formula is not giving an error but not pulling back any data either. Range 1 is the dates, range 2 is the presenters names. Should this work? or do I need to use a different formula?

=JOIN(COLLECT({2024-2025 Schedule-Full list (entire year) Range 1}, {2024-2025 Schedule-Full list (entire year) Range 2}, [Presenter Name]@row), ", ")

DKazatsky2

Hi @Vanessa Tanner,

In order to gather from multiple sheets you need to use separate JOINs. Give this a try.

=JOIN(COLLECT({2024-2025 Schedule-Full list (entire year) Range 1}, {Presenter Range 1}, @cell = [Presenter Name]@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({2024-2025 Schedule-Full list (entire year) Range 2}, {Presenter Range 2}, @cell = [Presenter Name]@row), CHAR(10))

Hope this helps,

Dave

Vanessa Tanner

Hello Dave, Thank you so much I was going to do 2024-2025 in one column and 2023-2024 in another column but even when I try to combine them nothing comes back in it's still blank with this formula.

Vanessa Tanner

@DKazatsky2 any other thoughts on things I might be able to try?

DKazatsky2

Hi @Vanessa Tanner,

Can you provide some screenshots of what you have, being sure to not include sensitive data?

Thanks.

Vanessa Tanner

@DKazatsky2

Sheet trying to bring dates formula into 2024-2025 column

Sheet bringing it in from

It is not giving an error and the formula says calculating but the field is still blank

=JOIN(COLLECT({2024-2025 Schedule-Full list (entire year) Range 1}, {Presenter 2024-2025 Schedule}, [Presenter Name]@row), CHAR(10))

DKazatsky2

@Vanessa Tanner

See if it works with adding in the "@cell" to the condition.

=JOIN(COLLECT({2024-2025 Schedule-Full list (entire year) Range 1}, {Presenter 2024-2025 Schedule}, @cell = [Presenter Name]@row), CHAR(10))

Vanessa Tanner

@DKazatsky2 I tried that with the same result it just comes back blank. I have also copied the name value from one sheet to the other so there is a 100% match and still nothing generates.

DKazatsky2

@Vanessa Tanner

The only other thing I can think of is to check your sheet references and ensure they are pointing to the correct columns. Otherwise, your setup looks just like mine, which is working.

Vanessa Tanner

@DKazatsky2 Alright latest and greatest in the saga. I inserted two rows on top and put in test data and the dates pull in fine. I notice when I go from one sheet to the other where the names are and not the two inserted rows it appears to be pulling in the dates and then the dates disappear. I have no project dependencies set-up and no automation. Any idea on what could be getting the values to disappear. I also tried coloring the text a different color that doesn't work or explain why I can see the data for the first few seconds I am on the sheet, I am at a loss.

DKazatsky2

@Vanessa Tanner You have me stumped on this one. No idea why you would be seeing that behavior.