Invalid Value Error in some rows of a Formula Column

Options

I have an inventory transaction sheet that I have been working on for a little bit now. Looking at my Stock Alerts column again, it looks like there is an issue.

The purpose of this column is to alert if the quantity requested is greater than or equal to the current quantity. If there is an error, then flag that the item as not listed in the source sheet. But taking the IFERROR out of the equation, I was not receiving a no match error, but an invalid value error. This is only happening on some of the rows and I'm not sure why.

Here the original formula:

=IFERROR(IF(AND((INDEX(COLLECT({Current Quantity}, {Part Code}, [Part Code]@row), 1)) <= Quantity@row, [Transaction Type]@row = "Request"), "REORDER NEEDED", "-"), "Item Not Listed")

Without IFERROR:

=IF(AND((INDEX(COLLECT({Current Quantity}, {Part Code}, [Part Code]@row), 1)) <= Quantity@row, [Transaction Type]@row = "Request"), "REORDER NEEDED", "-")

Any ideas on how to fix this?

Best Answer

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓
    Options

    Good question.

    I would use a helper column to check by simply entering an equals formula to see if it returns TRUE.

    I'm not sure of the name of the column where the Stock part is being requested, so adjust the following example to suit:

    [Part Code]@row = [Stock Request Code]@row

    Any other result suggests there is a difference between the codes, such as a space " " at the end of a code or after a hyphen, or an O has been entered as a 0, etc.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    Hi @Jade Boring

    I would paste the Index(Collect()) and then the Index(Collect())<= Quantity@row formula down temporary helper columns, to visually confirm there's no errors (and then delete the helper columns if no longer needed). If the column is too long for visual confirmation, an error with a sum formula at the end of the column should identify if there is any text, instead of all numbers, in the quantity fields.

    Also, per this other Community help post, I'd check that all the ranges are the same size/shape.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Jade Boring
    Jade Boring ✭✭✭✭
    Options

    @Jason Albrecht Thank you for your suggestion! I double checked and all the values are numbers, but I am still receiving the invalid value error. I did conditional formatting to flag if not a number and corrected all the identified cells.

    In the helper column I did the formula below and that is what's giving the error.

    =INDEX(COLLECT({Current Quantity}, {Part Code}, [Part Code]@row), 1)

    Could it be something in the part code that is causing an error? Here are some of the ones giving errors: 3-FH-FL-AIR100, 0-EL-TDR, O-PV-ISOBTL. All have matches in the reference sheet.

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓
    Options

    Good question.

    I would use a helper column to check by simply entering an equals formula to see if it returns TRUE.

    I'm not sure of the name of the column where the Stock part is being requested, so adjust the following example to suit:

    [Part Code]@row = [Stock Request Code]@row

    Any other result suggests there is a difference between the codes, such as a space " " at the end of a code or after a hyphen, or an O has been entered as a 0, etc.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Jade Boring
    Jade Boring ✭✭✭✭
    Options

    Thank you! I checked my references and they were looking at the wrong sheet. Once I corrected that, all the errors went away. Silly mistake.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Side note: The "Invalid Value" error coming from an INDEX/COLLECT is the same as a "No Match" in an INDEX/MATCH. Should help with future troubleshooting if you run into it again.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!