Index(Collect, or Join(Collect...?

Andrew Ryback · 01/03/25

Hi everyone,

Happy 2025.

I'm struggling with a multi-sheet formula.

I've built a sheet and am looking to pull project start date data from two independent sheets using a collect formula.

If I use an INDEX(COLLECT formula, I'm getting an #INVALID VALUE error.

If I use a JOIN(COLLECT formula, I'm getting the right data; however, I'm not able to format the results - it appears to be text only (I'd prefer to have it listed as Day, MM/DD/YYYY but it's defaulting to MM/DD/YY and cannot be changed).

Here's my INDEX(COLLECT forumla…

=INDEX(COLLECT({Start Date 2024}, {Project 2024}, PROJECT@row), 1) + INDEX(COLLECT({Start Date 2025}, {Project 2025}, PROJECT@row), 1)

…versus my JOIN(COLLECT formula…

=JOIN(COLLECT({Start Date 2024}, {Project 2024}, PROJECT@row), 1) + JOIN(COLLECT({Start Date 2025}, {Project 2025}, PROJECT@row), 1)

The JOIN(COLLECT is technically working, but not allowing me to format the results. I'm trying to duplicate this same formula for a contact list column and running into the same issues.

Any suggestions?

jmyzk_cloudsmart_jp · 01/04/25

Hi @Andrew Ryback

Regarding your formula for combining the 2024 and 2025 start dates for Project A (or any given project), could you clarify your desired output? Specifically:

Do you want to use MAX (latest start date) or MIN (earliest start date) between the two years?
- Example: If Project A starts on 01/15/2024 and 01/18/2025, do you want to show 01/18/2025 (MAX) or 01/15/2024 (MIN)?
Are you looking to combine the dates somehow (e.g., keeping the same month but aggregating years or days)?
- Please note that you cannot directly add two dates using the + operator, as Smartsheet treats dates as numeric values. Combining them would require a different approach.
Should the result focus on retaining specific details (like the month) or be displayed in a particular format?

Below are examples of some of the possible operations on two dates.

https://app.smartsheet.com/b/publish?EQBCT=d177dc7fec3c4c37ab3628f040aae936

Below are images of the sheets.

Sample Sheet 1: Main Sheet (Tracking Project Information)

Project Name	Start Date (Combined)
Project A	[Formula Output]
Project B	[Formula Output]
Project C	[Formula Output]

Sample Sheet 2: 2024 Projects

Project Name	Start Date 2024
Project A	01/15/2024
Project B	02/20/2024
Project C	03/10/2024

Sample Sheet 3: 2025 Projects

Project Name	Start Date 2025
Project A	01/18/2025
Project B	02/22/2025
Project C	03/12/2025

Andrew Ryback · 01/08/25

@jmyzk_cloudsmart_jp - thanks for the thorough response.

In the start date column of my sheet, if the project name matches an entry in my 2024 calendar, I want it to spit back that corresponding start date, OR, if the project name matches an entry in my 2025 calendar, I want it to spit back that corresponding start date.

I've been able to build this in the past when looking at one specific sheet (using the index-collect function), but have never tried a forumla that looks at multiple sheets to pull data.

The join-collect fuction that I tried does spit back accurate dates; however, they appear to be formatted as text, so I'm not sure this is the appropriate formula to use.

jmyzk_cloudsmart_jp · 01/09/25

Hi @Andrew Ryback

I updated the solution.

The goal is to retrieve the start date of a project from multiple sheets (Sheets 1 - 3, in this example) based on the project name.

Key Details About the Setup

[Preferred Start Date Method]# and [This Year]# are Sheet Summary fields:
- [Preferred Start Date Method]#: Allows the user to select either MIN (earliest date) or MAX (latest date) when multiple start dates exist for the same year.
- [This Year]#: Specifies the current year (e.g., 2025) and is used to filter the relevant start dates.

Key Steps in the Solution

Match Project Names Across Sheets
Each formula for [Sheet 1 Start], [Sheet 2 Start], and [Sheet 3 Start Date] searches for the project name in the respective sheet and returns the corresponding start date using INDEX and MATCH.

Example:
[Sheet 1 Start]=IF(HAS({Start Date Sheet 1 : Project Name}, [Project Name]@row), INDEX({Start Date Sheet 1: Start Date}, MATCH([Project Name]@row, {Start Date Sheet 1 : Project Name}, 0)))

HAS: Checks if the project name exists in the referenced column.

Combine Dates from Multiple Sheets
The [This Year] and [Last Year] columns use COLLECT to gather dates from all sheets that meet specific conditions, such as being valid dates (ISDATE) and belonging to a particular year.

Example: For the 2024 calendar:

=IF([Preferred Start Date Method]# = "MIN", 
    MIN(COLLECT([Sheet 1 Start]@row:[Sheet 3 Start Date]@row, 
                [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, 
                ISDATE(@cell), 
                [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, 
                YEAR(@cell) = [This Year]#)), 
    IF([Preferred Start Date Method]# = "MAX", 
       MAX(COLLECT([Sheet 1 Start]@row:[Sheet 3 Start Date]@row, 
                   [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, 
                   ISDATE(@cell), 
                   [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, 
                   YEAR(@cell) = [This Year]#))))

COLLECT: Filters dates that meet the criteria, the cell is a date, and the cell's Year value is 2024.

MIN/MAX: Select the earliest or latest date based on the user’s choice in [Preferred Start Date Method]#.

Final Output in [Start Date] Column

=IF(ISDATE([This Year]@row), 
    [This Year]@row, 
    IF(ISDATE([Last Year]@row), 
       DATE([This Year]#, MONTH([Last Year]@row), DAY([Last Year]@row))))

If [This Year}@row is a date, use the date; otherwise, if [Last Year]@row is a date, change the year to [This Year]#, specified in the Sheet Summary Field.

https://app.smartsheet.com/b/publish?EQBCT=e84d3efef7464d2db88cafa9a71a0c4d

All Formulas

[Start Date] ==IF(ISDATE([This Year]@row), [This Year]@row, IF(ISDATE([Last Year]@row), DATE([This Year]#, MONTH([Last Year]@row, DAY([Last Year]@row)))))

[Last Year] =IF([Preferred Start Date Method]# = "MIN", MIN(COLLECT([Sheet 1 Start]@row:[Sheet 3 Start Date]@row, [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, ISDATE(@cell), [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, YEAR(@cell) = [This Year]# - 1)), IF([Preferred Start Date Method]# = "MAX", MAX(COLLECT([Sheet 1 Start]@row:[Sheet 3 Start Date]@row, [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, ISDATE(@cell), [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, YEAR(@cell) = [This Year]# - 1))))
[This Year] =IF([Preferred Start Date Method]# = "MIN", MIN(COLLECT([Sheet 1 Start]@row:[Sheet 3 Start Date]@row, [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, ISDATE(@cell), [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, YEAR(@cell) = [This Year]#)), IF([Preferred Start Date Method]# = "MAX", MAX(COLLECT([Sheet 1 Start]@row:[Sheet 3 Start Date]@row, [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, ISDATE(@cell), [Sheet 1 Start]@row:[Sheet 3 Start Date]@row, YEAR(@cell) = [This Year]#))))

[Sheet 1 Start] =IF(HAS({Start Date Sheet 1 : Project Name}, [Project Name]@row), INDEX({Start Date Sheet 1: Start Date}, MATCH([Project Name]@row, {Start Date Sheet 1 : Project Name}, 0)))

[Sheet 2 Start] =IF(HAS({Start Date Sheet 2 : Project Name}, [Project Name]@row), INDEX({Start Date Sheet 2 : Start Date}, MATCH([Project Name]@row, {Start Date Sheet 2 : Project Name}, 0)))

[Sheet 3 Start Date] =IF(HAS({Start Date Sheet 3 : Project Name}, [Project Name]@row), INDEX({Start Date Sheet 3 : Start Date}, MATCH([Project Name]@row, {Start Date Sheet 3 : Project Name}, 0)))

Sample Start Dates Data

https://app.smartsheet.com/b/publish?EQBCT=53c61e36b18541ccba1858e795b38aaa

Index(Collect, or Join(Collect...?

Answers

Help Article Resources

Categories