Need assistance with collecting and then joining



I have a test master grid sheet where dates of leave (and different types of leave) are collected through a form.

Formula used for collecting dates together is:

=IF([End Date]@row <= [Start Date]@row, [Start Date]@row + "", JOIN([Start Date]@row:[End Date]@row, " - "))

I have a 2nd sheet from which I want to collect information from the master but for specific dates. For instance, only collect the dates together if the Start Date is between 6/1/20 and 6/30/20. I assume this is a combination of collect and join, but I have no idea which order to nest.

This is the formula I've determined so far, but now need to add the piece that collects for the specified dates. This cannot be a report because I will automate approvals on the data. I will substitute "June...) with the column-specific name. Screenshot of destination sheet below.

=JOIN(COLLECT({June Dates Together}, {June Email}, [Email/Contact]@row, {June A/L}, >1), " , ")

See Rebecca Panaccione on the bottom row. The above formula works to collect and combine all the dates from the Master as specific to Annual leave. That part works. I would like to fine tune this to collect the information for only the dates that start with 6/1/2020. Carry over is okay. It's the start date that drives the data.

As always, thank you so much for your time. You make me look smart!

Rebecca Panaccione

Best Answer


  • Genevieve,

    Thank you so much!


  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! Let me know if you have any questions about it 🙂

  • Sarah Underwood

    @Genevieve P

    I am trying to create a Join/Collect function however keep running into #No Match or #Incorrect argument. I am trying to join all the state abbreviations based on if the Branch NMLS# matches the Branch Roster NMLS#. I tried copy & pasting what you posted above and make it my own but keep running into issues.

    Here is the current code I have tried:

    =JOIN(COLLECT([State Abbr]:[State Abbr], [Branch NMLS#]:[Branch NMLS#] = [Branch Roster Branch NMLS]@row, [Branch Roster Branch NMLS]@row), " , ")

    With a result of #No Match.

    Please advise.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Sarah Underwood ,

    Since you have the criteria built into the sheet, you don't need to list it twice in your formula. Try this:

    =JOIN(COLLECT([State Abbr]:[State Abbr], [Branch NMLS#]:[Branch NMLS#], [Branch Roster Branch NMLS]@row), " , ")

    If this doesn't work, can I ask how your Branch NMLS column is being populated? It looks like the numbers might be recognized as text instead of numbers. You can either turn your Branch Roster Branch NMLS column into a text string by adding a ' in front of the numbers (ex. '1071) or you can adjust the formula in your Branch NMLS column to use the VALUE function and return a number.

    Let me know if this works for you!



  • Sarah Underwood

    @Genevieve P ,

    Thank you for your response. I fixed the initial spreadsheet to ensure the data was coming over as numbers vs text, re-uploaded, and updated the formula to no avail. It is coming up #No Match.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Sarah Underwood

    Your formula is written correctly, which is great! However #No Match indicates that it can't find the [Branch Roster NMLS] value in your range, so there's something blocking the formula from reading your values as the same type.

    What happened when you adding a ' in front of all the numbers that are criteria in your formula? For example:

    Branch Roster NMLS column




    Another test you can do, to make sure your numbers are numbers, is to put this formula in a helper column:

    =IF(ISNUMBER([Branch NMLS#]@row), "Yes", "No")

    This will return a "Yes" if the number in your primary column is being recognized as a number.

    Since the formula should work, it would be helpful to know how many of your values are returning #No Match. Can you try with the other values in the list and see if any of them work?

    If none of this has helped, how are you populating these two Branch columns, are you using a formula to create the numbers or are they manually typed in through a form or onto the sheet? I want to try and replicate the error you're receiving.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!