INDEX-COLLECT formula returning #INVALIDVALUE error

Anupriya

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!

Jeff Reisman

@Anupriya

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.

Jeff Reisman

@Anupriya

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.