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!
Best Answers
-
My apologies. The very first opening parenthesis needs removed as well.
-
It should work with any column type.
Answers
-
=(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)If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
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!
-
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), "")
-
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!
-
My apologies. The very first opening parenthesis needs removed as well.
-
WORKED PERFECTLY. ACTUALLY THANK YOU SO MUCH PAUL.
-
Happy to help. 👍️
-
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!
-
It should work with any column type.
-
You were spot on again, just had my references mixed up a bit.
THANK YOU PAUL!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!