Invalid Value Error in some rows of a Formula Column

Jade Boring

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?

Jason Albrecht

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.

Jason Albrecht

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.

Jade Boring

@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

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.

Jade Boring

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

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.