INDEXCOLLECT formula returning #INVALIDVALUE error
I'm trying to figure out a way to find the latest completed task in a Gantt. I'm using the formula below 
=INDEX(COLLECT([Task Name]:[Task Name], Finish:Finish, MAX(Finish:Finish), Status:Status, "Complete"), 1)
but I'm getting an invalid value error.
Thanks in advance!
Best Answer

There's nothing wrong with your formula structure. I built one with the same syntax and criteria and it worked as expected.
Below is the explanation for the invalid value error. It looks like you are looking for some value or criteria that can't possibly be there? Can you share a screenshot of your data and list the column names and column types?
#INVALID VALUE
Cause
The formula contains a number outside of the range that a function's argument expects. For example, this FIND formula that finds the character "H" in the text string "Hello" has a starting position of 100 characters...
=FIND("H", "Hello", 100)
...Since the string "Hello" is 5 characters long, it isn't possible to have FIND start looking for the character "H" at 100 characters in. Only the numbers 15 can be accepted.
Resolution
Make sure that all number arguments are within the range that the function expects.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

There's nothing wrong with your formula structure. I built one with the same syntax and criteria and it worked as expected.
Below is the explanation for the invalid value error. It looks like you are looking for some value or criteria that can't possibly be there? Can you share a screenshot of your data and list the column names and column types?
#INVALID VALUE
Cause
The formula contains a number outside of the range that a function's argument expects. For example, this FIND formula that finds the character "H" in the text string "Hello" has a starting position of 100 characters...
=FIND("H", "Hello", 100)
...Since the string "Hello" is 5 characters long, it isn't possible to have FIND start looking for the character "H" at 100 characters in. Only the numbers 15 can be accepted.
Resolution
Make sure that all number arguments are within the range that the function expects.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!