Getting #INVALID VALUE error with INDEX/COLLECT function

I'm trying to pull distinct values from a subset of a range. I'm using the following formula:

=INDEX(DISTINCT(COLLECT({Subject}, {Site Number}, {Metadata Site Number})), [Unique ID]@row, 0)

I'm looking to generate a list of distinct values that are for a specific subject (patient) that's at a specific site, which is referenced from a different file. Each time I apply this formula I get the #INVALID VALUE error.

I've successfully used this format in other files. Would appreciate any insights



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Your third {Range} should not be a cross sheet reference.

  • SashaZ
    SashaZ ✭✭✭

    Thanks Paul - I've read many of your answers - and delighted to have your attention on this!

    I solved for this by putting this value in the sheet. I've updated the formula as below...

    =INDEX(DISTINCT(COLLECT({Subject}, {Site Number}, Site@row)), [Unique ID]@row, 0)

    I still get the #INVALID VALUE error. Any thoughts?

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    edited 03/07/24

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try getting rid of the third portion of the INDEX function.

    =INDEX(DISTINCT(COLLECT({Subject}, {Site Number}, Site@row)), [Unique ID]@row)

    If that still doesn't work, lets try a temporary formula for some troubleshooting.

    =COUNT(DISTINCT(COLLECT({Subject}, {Site Number}, Site@row)))

  • SashaZ
    SashaZ ✭✭✭
    edited 03/07/24

    Removing the third portion of the INDEX function resulted in the same error.

    I converted to the Count formula suggested - I no longer get the #INVALID VALUE error, however it returns 0 (zero) instances, when I know there are 16. Part of the problem is that the referenced site value is a zero leading number - i.e., 02001. I've adjusted for this, by using a helper cell - with the following formula [Site@row + ""] - however it still comes back as zero. Thoughts?

  • SashaZ
    SashaZ ✭✭✭

    I found the resolution. instead of converting the value to text, I converted everything to a number value - i.e., I used


    This converted 01002 to 1002. There is no instance in our dataset where a site would be distinguished by the number of leading zeros. If that were the case, this solve wouldn't work - but for my purposes, we're good. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!