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
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!