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

Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 01/22/21 inFormulas and Functions

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

Payment Tracker:

image.png

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:

image.png

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.

.

我已经能够确定的问题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...

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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 formulashouldbe 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

Answers

  • L_123
    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.
    Genevieve P. Employee Admin
    Answer ✓

    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 formulashouldbe 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
    Leibel S ✭✭✭✭✭✭
    edited 01/22/21

    @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
    Paul Newcome ✭✭✭✭✭✭

    @Genevieve PThank you!!


    @L@123Well 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 SI 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 Phas discovered the issue is a known bug. At least now we know.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Andrée Staråand@Mike WildayHere'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
    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
    Paul Newcome ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi, <\/p>

Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>

=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>

Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"

Try this:<\/p>

=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/75169/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/75169/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions