Strange INVALID VALUE error

KristofKalocsai

Hi everyone,

I've encountered a problem in Smartsheet that I tried solving for a long time without success. I was creating metrics for a sheet, and I made a bunch of summary fields that contained my formulas. They all worked as intended when I created them. About a month passed without anyone touching them, I went back to check on them, and suddenly they were all displaying INVALID VALUE errors.

Below is a screenshot of my column headers and a relevant row

Here is the formula in question:

=INDEX(COLLECT(Sprint:Sprint; [Start Date]:[Start Date]; >=TODAY(); [Finish Date]:[Finish Date]; <=TODAY(); AncestorCount:AncestorCount; =1); 1)

The aim is to identify the currently running Sprint. It's supposed to display the text value in the Sprint column from the row where the start date is in the past, the finish date is in the future, and the number of ancestors is 1.

The type of the summary field is Text/Number, and the type of the Sprint column is dropdown (Tried changing it to text, problem is the same).

If I change the row index argument in the INDEX function from 1 to 0, the error changes to Invalid Column Value. However that makes no sense, since all columns are text, and I need a text value to be shown.

I tried replicating this function in a metric sheet with cross sheet references, and I am getting the same errors.

Thank you in advance for your help and ideas.

Paul Newcome

Try switching the arguments for the dates. You are currently filtering for rows with a start date in the future and a finish date in the past. You want to search for rows wiht start dates in the past and finish dates in the future.

Paul Newcome

Try switching the arguments for the dates. You are currently filtering for rows with a start date in the future and a finish date in the past. You want to search for rows wiht start dates in the past and finish dates in the future.

KristofKalocsai

Thank you, Paul!

I really don't know how that happened, as the formula was working for me before, then it stopped. Because of that, it must have been the only part of the formula I did not question. Great catch, thanks again for the fresh eyes!

Paul Newcome

Happy to help. 👍️