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

STA13

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?

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!

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:

="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!