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
Check out the Formula Handbook template!