Why is my Index Collect formula not working?

Mikehud83

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.

Paul McGuinness

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

Mikehud83

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!

Amit Wadhwani

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.

Mikehud83

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

Mikehud83

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

Sam Swain

INDEX MATCH function also resolves the issue where the criteria is a number that starts with 0