INDEX-COLLECT 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 1-5 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 1-5 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
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!