Getting #INVALID VALUE error with INDEX/COLLECT function

SashaZ

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

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

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

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

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

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

=Value(Site@row)

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.