IFERROR(INDEX(COLLECT Pulling Data from 3 different sheets into one single column

Good Afternoon Smartsheet Community!

I have been working on a formula that pulls data from single select columns on 3 different cross sheets into one "home" sheet. My current formula is shown below. Currently getting an "#Incorrect Argument Set" error message.

=(IFERROR(INDEX(COLLECT({Pass/Fail}), {Unit Part Number}, @cell = [Part #]@row, {Sales Order Number}, @cell = Order@row, 1), IFERROR(INDEX(COLLECT({Pass/Fail 1}), {Unit Part Number 1}, @cell = [Part #]@row, {Sales Order Number 1}, @cell = Order@row, 1), IFERROR(INDEX(COLLECT({Pass/Fail 2}), {Unit Part Number 2}, @cell = [Part #]@row, {Sales Order Number 2}, @cell = Order@row, 1)))))

I am using two unique identifiers from the cross sheets to match with the "home" sheet I am trying to pull the data into, they would be the "Part Number" and "Order" columns on all 4 sheets, if on the cross sheets these match the data on the "home" sheet I want it to pull what is displayed in the "Pass/Fail" columns of these sheets.

Is it possible to perform this formula from 3 different cross sheets in one single column?

Thanking everyone in advance for any help you may have!

Find more posts tagged with

Accepted answers

Paul Newcome

My apologies. The very first opening parenthesis needs removed as well.

Paul Newcome

It should work with any column type.

All comments

Mark.poole

@Matthew R

=(IFERROR(INDEX(COLLECT({Pass/Fail}), {Unit Part Number}, @cell = [Part #]@row, {Sales Order Number}, @cell = Order@row, 1)+” “+IFERROR(INDEX(COLLECT({Pass/Fail 1}), {Unit Part Number 1}, @cell = [Part #]@row, {Sales Order Number 1}, @cell = Order@row, 1)+” “+IFERROR(INDEX(COLLECT({Pass/Fail 2}), {Unit Part Number 2}, @cell = [Part #]@row, {Sales Order Number 2}, @cell = Order@row, 1)

Matthew R

Hey Mark!

I am currently just getting an #UNPARSEABLE error code when using that formula.

I will tweak it a bit to try and get it to work, probably something I am missing on my end.

Thank you very much for the help!

Paul Newcome

Try retyping those quotes. Looks like "Smart Quotes" were used which (ironically enough) are not recognized as valid characters in Smartsheet formulas. Also have some parenthesis that need adjusting.

=(IFERROR(INDEX(COLLECT({Pass/Fail}), {Unit Part Number}, @cell = [Part #]@row, {Sales Order Number}, @cell = Order@row), 1), "")+" "+IFERROR(INDEX(COLLECT({Pass/Fail 1}), {Unit Part Number 1}, @cell = [Part #]@row, {Sales Order Number 1}, @cell = Order@row), 1), "")+" "+IFERROR(INDEX(COLLECT({Pass/Fail 2}), {Unit Part Number 2}, @cell = [Part #]@row, {Sales Order Number 2}, @cell = Order@row), 1), "")

Matthew R

I still seem to be coming up with an "#UNPARSEABLE" error message. I think I have made sure there are no smart quotes within my formula. Perhaps my structure is a bit off at this point.

Thank you for the help Paul!

Paul Newcome

My apologies. The very first opening parenthesis needs removed as well.

Matthew R

WORKED PERFECTLY. ACTUALLY THANK YOU SO MUCH PAUL.

Paul Newcome

Happy to help. 👍️

Matthew R

Would this formula work if I was pulling data from a "Text" column as well? Or does it only work when pulling data from "Dropdown List" columns?

Hopefully that makes sense.

THANK YOU AGAIN!

Paul Newcome

It should work with any column type.

Matthew R

You were spot on again, just had my references mixed up a bit.

THANK YOU PAUL!

Paul Newcome

Happy to 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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.