Index(Collect( function not working. Can you help me to resolve

I am utilizing a formula that I have used between other sheets, so that I can type 2 pieces of information in and it populates the information that I need on to the sheet from there.

Here was the original formula (ps i set this up so that I could obtain contact information for 2 parties off 1 submission.)

=IFERROR(INDEX(COLLECT({MiSTRLF NOI Org Add}, {MiSTRLF NOI Primary Contact Last Name}, [Last Name]@row, {MiSTRLF NOI Org}, Organization@row), 1), (INDEX(COLLECT({MiSTRLF NOI Org Add}, {MiSTRLF NOI Secondary LName}, [Last Name]@row, {MiSTRLF NOI Org}, Organization@row), 1)))

I can type in the organization and the last name and it will pull all the contact information to create a list of contacts for me. It works perfectly.

Now I am trying to recreate this for another form that I need to create which I will then offload into excel. Here is the function that I am trying to use:

=INDEX(COLLECT({Project Title}, {Primary Column}, [Project Number]@row, {Loan Applicant}, [Loan Recipient]@row), 1)

The Project title, Primary column, Loan Applicant are all coming from my source sheet. The Project Number/Loan Recipient @row are referencing a cell in that row where I would populate the matching information.

What I do not understand is it will not pull data from the other sheet when I type in the Project Number and Loan Recipient information. I know it is exact as I have copied it exactly from the source sheet. I am following the same set up and yet I get #Invalid Value.

Am I approaching this wrong?

I also want to make this pull in multiple items into one cell once I can confirm that this will work.

For example, I want to pull in from the other sheet the Scope of Work, Damage history, as well as a few other items into one field to summarize the project. Any idea how I would do this?

Find more posts tagged with

Accepted answers

STA13

I was able to figure this out! I found that the format of the column was preventing the formula to work.

Now I need to figure out how to Join(Collect( information as I have not used that before! I may be back with an additional formula requesting help! Thank you both for responding!

STA13

Just an update :)

I am using this formula to combine values into one cell on my current spread sheet:

="Primary Natural Hazard: " + IF(COUNT(COLLECT({Primary Hazard}, {Primary Column}, [Project Number]@row)) > 0, JOIN(COLLECT({Primary Hazard}, {Primary Column}, [Project Number]@row), ", "), "No response from applicant") + CHAR(13) + CHAR(10) + "Additional Natural Hazards: " + IF(COUNT(COLLECT({Additional natural hazards}, {Primary Column}, [Project Number]@row)) > 0, JOIN(COLLECT({Additional natural hazards}, {Primary Column}, [Project Number]@row), ", "), "No response from applicant")

This is working out great for all combos that I need (of course within character limitations)

Thank you again for your help!

All comments

Adam Murphy

It sounds like one of your ranges is either not defined or is defined as a range of cells that is larger than the others. I would check the cross-sheet references to make sure they match in scope. Also make sure column types match between sheets.

You will want to use JOIN/COLLECT to bring multiple values into a single cell from another sheet.

Hope this helps!

STA13

I double checked all column types, and they are all Text/Number, I only selected one column for my range. For the {Project Title} I entered the formula, selected reference another sheet, selected the desired source sheet and then found the project title column and highlighted it by selecting the header, I did identify a sheet reference name for the column before updating the reference.

Should I only be selecting the cell that I want the info to come from?

Shouldn't it search the column based on identifying the correct project number / loan applicant?

I made this a column formula, and it still did not populate any information.

STA13

I followed this same process for the past formula, so I guess I am just very confused and frustrated on why this is not working for me.

Maria Hill

Is it because of your If error not having a function? If you were to put "" before the very last parenthesis does that help?

STA13

If I input the following:

=INDEX(COLLECT({Project Title}, {Primary Column}, [Project Number]@row, {Loan Applicant}, [Loan Recipient]@row), 1, "")

The error message that it now gives me is: #INVALID DATA TYPE

Previously it was providing me with #INVALID VALUE

So unfortunately, I am still getting an error. I think I am going to create a new spread sheet just to see if starting clean works?? I read another post where the user started from scratch and then the formula worked.

Although I am still open to any help I can get!! 😊 Thank you for trying!!

STA13

I was able to figure this out! I found that the format of the column was preventing the formula to work.

Now I need to figure out how to Join(Collect( information as I have not used that before! I may be back with an additional formula requesting help! Thank you both for responding!

STA13

Just an update :)

I am using this formula to combine values into one cell on my current spread sheet:

This is working out great for all combos that I need (of course within character limitations)

Thank you again for your help!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.