#INVALID VALUE Error  Can't figure out why...
I am working with two sheets. "Payment Tracker" and "Job Tracker".
Payment Tracker:
Auto: AutoNumber 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: Autonumber 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...
Best Answer

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 crosssheet 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

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.

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 crosssheet 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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...

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

@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

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

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

@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 THDAfter HoursWK1} and so on) is the same in both formulas.
I cannot figure out what the issue is.
Any thoughts? 
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!SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

This is the formula that works;
=IF([Odd or Even?]24 = 1, IF(INDEX(COLLECT({ODD MTH THDAfter HoursWK1}, {ODD MTH THDAfter HoursWK1 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THDAfter HoursName}, {ODD MTH THDAfter HoursWK1 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THDAfter HoursWK2}, {ODD MTH THDAfter HoursWK2 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THDAfter HoursName}, {ODD MTH THDAfter HoursWK2 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THDAfter HoursWK3}, {ODD MTH THDAfter HoursWK3 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THDAfter HoursName}, {ODD MTH THDAfter HoursWK3 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THDAfter HoursWK4}, {ODD MTH THDAfter HoursWK4 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THDAfter HoursName}, {ODD MTH THDAfter HoursWK4 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THDAfter HoursWK5}, {ODD MTH THDAfter HoursWK5 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THDAfter HoursName}, {ODD MTH THDAfter HoursWK5 TOC}, HAS(@cell, [LookUp Value]16)))))))), IF(INDEX(COLLECT({EVEN MTH THDAfter HoursWK1}, {EVEN MTH THDAfter HoursWK1 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THDAfter HoursName}, {EVEN MTH THDAfter HoursWK1 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THDAfter HoursWK2}, {EVEN MTH THDAfter HoursWK2 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THDAfter HoursName}, {EVEN MTH THDAfter HoursWK2 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THDAfter HoursWK3}, {EVEN MTH THDAfter HoursWK3 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THDAfter HoursName}, {EVEN MTH THDAfter HoursWK3 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THDAfter HoursWK4}, {EVEN MTH THDAfter HoursWK4 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THDAfter HoursName}, {EVEN MTH THDAfter HoursWK4 TOC}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THDAfter HoursWK5}, {EVEN MTH THDAfter HoursWK5 TOC}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THDAfter HoursName}, {EVEN MTH THDAfter HoursWK5 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 THDAfter HoursWK1}, {ODD MTH THD On CallAfter HoursWK1 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THDAfter HoursName}, {ODD MTH THD On CallAfter HoursWK1 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THDAfter HoursWK2}, {ODD MTH THD On CallAfter HoursWK2 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THDAfter HoursName}, {ODD MTH THD On CallAfter HoursWK2 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THDAfter HoursWK3}, {ODD MTH THD On CallAfter HoursWK3 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THDAfter HoursName}, {ODD MTH THD On CallAfter HoursWK3 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THDAfter HoursWK4}, {ODD MTH THD On CallAfter HoursWK4 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THDAfter HoursName}, {ODD MTH THD On CallAfter HoursWK4 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({ODD MTH THDAfter HoursWK5}, {ODD MTH THD On CallAfter HoursWK5 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({ODD MTH THDAfter HoursName}, {ODD MTH THD On CallAfter HoursWK5 SOC Store #'s}, HAS(@cell, [LookUp Value]16)))))))), IF(INDEX(COLLECT({EVEN MTH THDAfter HoursWK1}, {EVEN MTH THD After HoursWK1 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THDAfter HoursName}, {EVEN MTH THD After HoursWK1 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THDAfter HoursWK2}, {EVEN MTH THD After HoursWK2 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THDAfter HoursName}, {EVEN MTH THD After HoursWK2 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THDAfter HoursWK3}, {EVEN MTH THDAfter HoursWK3 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THDAfter HoursName}, {EVEN MTH THDAfter HoursWK3 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THDAfter HoursWK4}, {EVEN MTH THD After HoursWK4 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THDAfter HoursName}, {EVEN MTH THD After HoursWK4 SOC Store #'s}, HAS(@cell, [LookUp Value]16))), IF(INDEX(COLLECT({EVEN MTH THDAfter HoursWK5}, {EVEN MTH THD After HoursWK5 SOC Store #'s}, HAS(@cell, [LookUp Value]16)), 1) = 1, JOIN(COLLECT({EVEN MTH THDAfter HoursName}, {EVEN MTH THD After HoursWK5 SOC Store #'s}, HAS(@cell, [LookUp Value]16))))))))) 
Thanks!
Can you share some screenshots? (Please delete/replace any confidential/sensitive information before sharing) That would make it easier to help.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

The solution is a 'Technician On Call'  Identifies who to call based on Store # and call cadence/schedule.
There is a Lookup SheetAnd 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.
Help Article Resources
Categories
Check out the Formula Handbook template!