#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.

PeggyLang

@Paul Newcome
I appear to be having a VERY similar issue with a formula throwing 'Invalid Value' error.

Above formula works beautifully.

I have another formula EXACTLY (I believe) the same as the above but referencing a different column than those highlight in yellow above and the result is 'Invalid Value'.

Same source sheet, the first reference ('{ODD MTH THD-After Hours-WK1} and so on) is the same in both formulas.

I cannot figure out what the issue is.

Any thoughts?

Andrée Starå

https://community.smartsheet.com/discussion/comment/441587#Comment_441587

Hi,

I hope you're well and safe!

Can you paste the formulas as text instead?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

PeggyLang

This is the formula that works;

=IF([Odd or Even?]24 = 1, IF(INDEX(COLLECT({ODD MTH THD-After Hours-WK1}, {ODD MTH THD-After Hours-WK1 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THD-After Hours-Name}, {ODD MTH THD-After Hours-WK1 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THD-After Hours-WK2}, {ODD MTH THD-After Hours-WK2 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THD-After Hours-Name}, {ODD MTH THD-After Hours-WK2 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THD-After Hours-WK3}, {ODD MTH THD-After Hours-WK3 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THD-After Hours-Name}, {ODD MTH THD-After Hours-WK3 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THD-After Hours-WK4}, {ODD MTH THD-After Hours-WK4 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THD-After Hours-Name}, {ODD MTH THD-After Hours-WK4 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THD-After Hours-WK5}, {ODD MTH THD-After Hours-WK5 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THD-After Hours-Name}, {ODD MTH THD-After Hours-WK5 TOC}, HAS(@cell, [LookUp Value]16)))))))), IF(INDEX(COLLECT({EVEN MTH THD-After Hours-WK1}, {EVEN MTH THD-After Hours-WK1 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THD-After Hours-Name}, {EVEN MTH THD-After Hours-WK1 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THD-After Hours-WK2}, {EVEN MTH THD-After Hours-WK2 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THD-After Hours-Name}, {EVEN MTH THD-After Hours-WK2 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THD-After Hours-WK3}, {EVEN MTH THD-After Hours-WK3 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THD-After Hours-Name}, {EVEN MTH THD-After Hours-WK3 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THD-After Hours-WK4}, {EVEN MTH THD-After Hours-WK4 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THD-After Hours-Name}, {EVEN MTH THD-After Hours-WK4 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THD-After Hours-WK5}, {EVEN MTH THD-After Hours-WK5 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THD-After Hours-Name}, {EVEN MTH THD-After Hours-WK5 TOC}, HAS(@cell, [LookUp Value]16)))))))))

This is the formula that is throwing 'Invalid Value' error;

=IF([Odd or Even?]24 = 1, IF(INDEX(COLLECT({ODD MTH THD-After Hours-WK1}, {ODD MTH THD On Call-After Hours-WK1 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THD-After Hours-Name}, {ODD MTH THD On Call-After Hours-WK1 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THD-After Hours-WK2}, {ODD MTH THD On Call-After Hours-WK2 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THD-After Hours-Name}, {ODD MTH THD On Call-After Hours-WK2 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THD-After Hours-WK3}, {ODD MTH THD On Call-After Hours-WK3 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THD-After Hours-Name}, {ODD MTH THD On Call-After Hours-WK3 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THD-After Hours-WK4}, {ODD MTH THD On Call-After Hours-WK4 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THD-After Hours-Name}, {ODD MTH THD On Call-After Hours-WK4 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THD-After Hours-WK5}, {ODD MTH THD On Call-After Hours-WK5 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THD-After Hours-Name}, {ODD MTH THD On Call-After Hours-WK5 SOC Store #'s}, HAS(@cell, [LookUp Value]16)))))))), IF(INDEX(COLLECT({EVEN MTH THD-After Hours-WK1}, {EVEN MTH THD After Hours-WK1 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THD-After Hours-Name}, {EVEN MTH THD After Hours-WK1 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THD-After Hours-WK2}, {EVEN MTH THD After Hours-WK2 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THD-After Hours-Name}, {EVEN MTH THD After Hours-WK2 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THD-After Hours-WK3}, {EVEN MTH THD-After Hours-WK3 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THD-After Hours-Name}, {EVEN MTH THD-After Hours-WK3 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THD-After Hours-WK4}, {EVEN MTH THD After Hours-WK4 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THD-After Hours-Name}, {EVEN MTH THD After Hours-WK4 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THD-After Hours-WK5}, {EVEN MTH THD After Hours-WK5 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THD-After Hours-Name}, {EVEN MTH THD After Hours-WK5 SOC Store #'s}, HAS(@cell, [LookUp Value]16)))))))))

Andrée Starå

https://community.smartsheet.com/discussion/comment/441607#Comment_441607

Thanks!

Can you share some screenshots? (Please delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

PeggyLang

The solution is a 'Technician On Call' - Identifies who to call based on Store # and call cadence/schedule.
There is a Lookup Sheet

And then there are a couple of worksheets that contain the data/schedule, i.e., technician name, supervisor name, Week # and store #'s.

I have utilized a few 'hidden columns' to determine whether the current month is an odd or even #'d, month, what week the current date is in.
Both formulas analyze whether it is an odd or even #'d month; June = 6 = Even
Both formulas analyze what week the current date is; i.e., Jun 19 is 'WK3'
Then the formula determines what 'Name' is associated with 'Store #' (from Lookup sheet) for the current week.
The formula which results in the 'technician on call' works wonderfully.
The formula which should result in the 'supervisor on call' is throwing '#Invalid Value'.
Orange columns (oops and 'EVEN MTH WK1 SOC Store #'s - I forgot to color orange) are drop down, multi select; WK# columns are a check and 'EVEN MTH WK# Sup OC' columns are text. These columns are only used in formula that sits in the 'EVEN MTH WK# SOC Store #'s' which produces the list of stores that the Supervisor is responsible for.