Why is my Index Collect formula not working?
My source sheet contains information associated with clients, including site locations, managers, addresses, email, and phone numbers. My destination sheet includes rows with that site information. In some cases, a client has multiple sites. To capture all of the sites (rows) associated with a particular client, I added a column with site numbers (01,02,03, etc.) so that the formula looks for the client name and the site number. Here's the formula:
Unfortunately, I'm getting an Incorrect Argument Set error.
Thanks in advance for any help you can provide.
Answers
-
HI @Mikehud83
You just need to correct the end of the formula by adding
, 1)
Should read
=INDEX(COLLECT({Direct Data Range 7}, {Direct Data Range 2},[Primary Column]1, {Direct Data Range 13}, [Site Add]@row), 1)
Hope that helps
Paul
-
Hi, Paul. Thank you for your response. I updated my formula as instructed:
Unfortunately, I'm now getting an Invalid Value error. When I looked up "Smartsheet Invalid Value," I found an article indicating a bug for numbers using leading zeroes, and that applied to my {Direct Data Range 13} and [Site Add]@Row, so I removed the leading zeroes on both sheets, resaved, and tried again, preceding the two elements with an equal sign as recommended in the article. This time, I got an Invalid Operation error.
Any suggestions? Thanks!
-
Hi @Mikehud83
I am assuming the values in the Site Add columns are 01, 02, 03, etc. I would suggest you can try VALUE function. If it starting from 0. You can try the below formula it is working for me.
=INDEX(COLLECT({Direct Data Range 7}, {Direct Data Range 2}, [Primary Column]@row, {Direct Data Range 13}, [Site Ad]@row), 1)
Let me know if this works.
Best Regards
Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA
https://www.linkedin.com/in/amitinddr/
Did my solution help you? Do not forget to hit the awesome icon.
Best Regards
Amit Wadhwani, Smartsheet Community Champion
Smartsheet CoE, Ignatiuz, Inc., Exton, PA
Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"https://www.linkedin.com/in/amitinddr/ -
Hi, Amit,
Unfortunately, when I pasted the formula in, I received an UNPARSEABLE error. I used the Value function for the number in the other sheet, and it still had an UNPARSEABLE error. Thank you for trying, though!
Regards,
Mike Hudnall
-
Hi, Amit,
I got the formula to work. I did so by starting the formula from scratch in each case, in combination with using the Value function as you suggested. I'm not sure why it was necessary for me to start each formula from scratch, but it works now, and I thank you!
Regards,
Mike Hudnall
-
INDEX MATCH function also resolves the issue where the criteria is a number that starts with 0
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!