#INVALID VALUE Error - Can't figure out why...

Paul Newcome

I am working with two sheets. "Payment Tracker" and "Job Tracker".

Payment Tracker:

Auto: Auto-Number Column - No formatting

Row: =MATCH(Auto@row, Auto:Auto, 0)

Invoice Number: Primary Column - Manual Entry

Invoice Amount: Text/number column

=INDEX({Job Tracker Invoice Amount}, MATCH([Invoice Number]@row, {Job Tracker Invoice Number}, 0))

Payment Date: Date column - Manual Entry

Payment Amount: Text/number column formatted for USD - Manual Entry

Outstanding Balance: Text/number column formatted for USD

=[Invoice Amount]@row - SUMIFS([Payment Amount]:[Payment Amount], [Invoice Amount]:[Invoice Amount], [Invoice Amount]@row, Row:Row, @cell >= Row@row)

.

.

Job Tracker:

Invoice Number: Auto-number column with 0000 fill

Final Payment Received On: Date Column

=INDEX(COLLECT({Payment Tracker Payment Date}, {Payment Tracker Invoice Number}, [Invoice Number]@row, {Payment Tracker Outstanding Balance}, @cell = 0), 1)

Payment Amount: Text/number Column formatted for USD

=SUMIFS({Payment Tracker Payment Amount}, {Payment Tracker Invoice Number}, [Invoice Number]@row)

.

.

Goal: Pull date from "Payment Tracker" where Invoice Number matches and Outstanding balance is $0.00.

.

I have been able to determine that the issue with the formula in the "Job Tracker" sheet's [Final Payment Received On] column is here:

{Payment Tracker Invoice Number}, [Invoice Number]@row

What I don't understand is why that section works just fine in the [Payment Amount] column's SUMIFS, but throws an error when trying to pull the date in the COLLECT function. If I remove that section, the formula works fine. If I leave that as the only range/criteria then it continues with the error.

I have already tried logging out, clearing cookies and cache, then logging back in with no change. I have also tried other browsers with no success.

Does anyone have any ideas/suggestions? I don't want to save the sheet as new because I have the current form linked to an active website and would prefer to be able to leave the website alone if possible.

Just not sure why that range/criteria set would work in one formula and not the other within the same sheet...

Genevieve P.

Hi @Paul Newcome

I was able to recreated exactly what you're describing here, and I can confirm that there is currently a known bug that our product team is looking into.

This error has been seen in cross-sheet formulas using numbers with leading 0's as criteria, even when there is a match in the other sheet. To get around this error for now, add an = sign before the criteria, like this:

=INDEX(COLLECT({Payment Tracker Payment Date}, {Payment Tracker Invoice Number}, =[Invoice Number]@row, {Payment Tracker Outstanding Balance}, @cell = 0), 1)

Although in most cases the formula should be fine without the = sign, this is a specific issue with numbers that are recognized as text. Our Product team is aware of this, and I have passed along a link to this thread as another example of the issue. Thanks for providing such a detailed explanation and troubleshooting steps!

Cheers,

Genevieve

L_123

I don't see any syntax or formula issues, and I went through it fairly thoroughly. Have you tried changing the column type of the payment date to text/number, saving it, entering the other sheet, saving that one, then fixing it back? I've had to do this in the past to fix some issues.

That said, I think it might be to do with smartsheet servers running slow. They are probably doing that dashboard update they were talking about right now. I was having issues with an rgb ball returning text instead of boolean today unless I let it load for a while.

Genevieve P.

Hi @Paul Newcome

I was able to recreated exactly what you're describing here, and I can confirm that there is currently a known bug that our product team is looking into.

This error has been seen in cross-sheet formulas using numbers with leading 0's as criteria, even when there is a match in the other sheet. To get around this error for now, add an = sign before the criteria, like this:

=INDEX(COLLECT({Payment Tracker Payment Date}, {Payment Tracker Invoice Number}, =[Invoice Number]@row, {Payment Tracker Outstanding Balance}, @cell = 0), 1)

Although in most cases the formula should be fine without the = sign, this is a specific issue with numbers that are recognized as text. Our Product team is aware of this, and I have passed along a link to this thread as another example of the issue. Thanks for providing such a detailed explanation and troubleshooting steps!

Cheers,

Genevieve

Leibel S

@Paul Newcome

It has to do with the fact that your invoice numbers start with 0. Because of that smartsheet adds a ' in the beginning of the number to keep it as text. As such both your formulas are not returning anything.

Your Sumif formula just returns 0.00, the index / collect throws an error because the collect range is empty and the index needs a range to run on...

Paul Newcome

@Genevieve P Thank you!!

@L@123 Well that answers this question, but doesn't help with your speed issues. I'd rather see some time functions/formatting, but I guess an upgrade to the dashboards is better than nothing. Hahaha

@Leibel S I hadn't even noticed that the SUMIFS was producing the wrong amount yet. I was still trying to work through the error. Using your line of thinking though, if they were both text and had the hidden ' then there should still be a match (theoretically). It looks like @Genevieve P has discovered the issue is a known bug. At least now we know.

Paul Newcome

@Andrée Starå and @Mike Wilday Here's a little bug for you to look out for. I usually use @cell references in my formulas so had never run into it before. Apparently the one time I decide not to use "@cell = " is the one time where you at least need the "=". Haha

Leibel S

@Paul Newcome

You will still need an error catcher on the index collect because it will run empty if there are no payments yet...

Paul Newcome

@Leibel S Yeah. I initially had the IFERROR to leave it blank, but then it was coming up blank when it shouldn't be. I removed the IFERROR for troubleshooting.