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

Options
Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 01/22/21 in Formulas and Functions

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

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

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

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    edited 01/22/21
    Options

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

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

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

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

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

    @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å
    Andrée Starå ✭✭✭✭✭✭
    Options

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

    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å ✭✭✭✭✭✭
    Options

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

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!