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

Paul Newcome
Paul Newcome Community Champion
edited 01/22/21 in Formulas 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.

.

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

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    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 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

    Need more information? πŸ‘€ | Help and Learning Center

    こんにけは (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!πŸ‘‹ | Global Discussions

Answers

  • L_123
    L_123 Community Champion

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

    Need more information? πŸ‘€ | Help and Learning Center

    こんにけは (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!πŸ‘‹ | Global Discussions

  • Leibel S
    Leibel S Community Champion
    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 Community Champion

    @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
    Paul Newcome Community Champion

    @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
    Leibel S Community Champion

    @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 Community Champion

    @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
    PeggyLang ✭✭✭✭✭✭

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

    image.png

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

    image.png


    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Γ₯
    AndrΓ©e StarΓ₯ Community Champion

    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.

  • PeggyLang
    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Γ₯
    AndrΓ©e StarΓ₯ Community Champion

    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.

  • PeggyLang
    PeggyLang ✭✭✭✭✭✭

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

    image.png

    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.

    image.png

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!