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
Answers

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

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?

Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile  Open to work

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

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?

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!