Formatting Issues with Index-Collect Formula

Hi all,

I'm having some issues with an index-collect formula.

I have (3) existing sheets that list a unique project name (text field), as well as a producer assigned to work that project. The producer column in all (3) sheets is set as a contact field.

In a separate sheet, I'm trying to auto-populate the producer assignment using the unique project name.

The formula below works in populating the correct name; however, it's not populating this as a contact field - they're all populating as text fields. For context, the column I'm inputting the formula into is set as a contact field.

I have had success in populating this as a contact field in the past, but only when referencing (2) sheets. For some reason, adding a 3rd is giving me issues.

Any thoughts on how to solve this?

=IFERROR(INDEX(COLLECT({2024 Producer}, {2024 Project}, PROJECT@row ), 1), "") + IFERROR(INDEX(COLLECT({2025 PRODUCER}, {2025 Project}, PROJECT@row ), 1), "") + IFERROR(INDEX(COLLECT({2026 Producer}, {2026 Project}, PROJECT@row ), 1), "")

Find more posts tagged with

Formulas

INDEX

Accepted answers

All comments

Paul Newcome

It is because you are adding them together. The IFERROR is outputting "" which turns anything that follows into a text string.

Instead of adding them together, nest them.

=IFERROR(IFERROR(IFERROR(INDEX(COLLECT(first sheet), 1), INDEX(COLLECT(second sheet), 1)), INDEX(COLLECT(third sheet), 1)), "")

Andrew Ryback

Thanks, Paul. I gave this a try, but it's spitting back a circular reference warning.

Here's what I've entered.

=IFERROR(IFERROR(IFERROR(INDEX(COLLECT({2024 Producer}, {2024 Project}, PRODUCER@row ), 1), INDEX(COLLECT({2025 PRODUCER}, {2025 Project}, PROJECT@row ), 1), INDEX(COLLECT({2026 Producer}, {2026 Project}, PROJECT@row ), 1)), ""))

Paul Newcome

Make sure it is not in one of the columns being referenced. You are referencing both the PRODUCER and PROJECT columns.

Andrew Ryback

The producer and yearly project columns (i.e. 2024 project, 2025 projects, etc) are all unique, but project@row is being referenced multiple times. Does that somehow need to be adjusted?

Paul Newcome

No. You just can't put the formula into the Producer column or the Project column (the columns referenced with "@row").

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.