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.


  • Paul McGuinness
    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


  • 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
    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


    Did my solution help you? Do not forget to hit the awesome icon.

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE, Ignatiuz Software, Exton, PA


    Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"

  • 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!


    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!


    Mike Hudnall

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!