If Error to fix "Invalid Column Value"

EEK

What's the formula for fixing Invalid column types?

Specific example:

I have a text/number column for info from another sheet; one cell is a date and I'm getting the "Invalid Column Value" error.

Smartsheet suggests this help article

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--invalid-column-value

And gives the example of this for a text/number =MAX([Due Date]1, [Due Date]52)

with this fix: =IFERROR(“Not a number!”, MAX([Due Date]1, [Due Date]5))

So I tried it for my formula =INDEX({Q120}, MATCH([Task Name]$1, {FacilityQ120}, 0))

(all my other text/number formulas with this same format work)

=IFERROR(“Not a number!”,INDEX({Q120}, MATCH([Task Name]$1, {FacilityQ120}, 0)))

and it comes back unparsable. Can someone help me with this error and in general what's a good way to bypass this "Invalid column value" error?

Paul Newcome

The IFERROR is not necessary for (and won't work on) an invalid column value when trying to pull a date value into a text/number column. To do this, you need to convert the date value into a text string by "adding nothing".

=INDEX(……., MATCH(……)) + ""

(plus quote quote)

Paul Newcome

Right. You wouldn't use the IFERROR. If you still want to use it, you need to have an output for it if the INDEX/MATCH is throwing an error.

Georgie

Hi @EEK,

It looks like you’ve spotted something in our documentation that needs updating - thanks for pointing this out! 

Having checked out the help article for the IFERROR Function, I confirmed that the syntax for IFERROR is to include your existing formula first, and then the value you want to display if there’s an error. So, your formula would be:

• =IFERROR(INDEX({Q120}, MATCH([Task Name]$1, {FacilityQ120}, 0)), “Not a number!”) 

I’ve raised feedback on the Formula error messages help article internally to get the example updated - I’d recommend also raising feedback on it if you have time. To do so, scroll to the bottom of the article and click “No” under “Was this article helpful?” - you’ll then be able to add your comments about what needs changing.

Another point to mention - I see that you’re referencing a specific cell in your formula. If you want to apply the formula to all rows in the sheet, and you’re looking to match the Task Name for each row in your sheet with cells in the FacilityQ120 range, you could swap “$1” for “@row”. Check out this help article for more information: Create efficient formulas with @cell and @row. Once you’ve done that, you can then right-click on the cell and click “Convert to column formula” to apply it to all rows - more on this here.

Thanks again,

Georgie 

EEK

Thanks so much! Unfortunately, the formula you generously provided comes back #UNPARSEABLE. Context on the $1 is below in italics.

• =IFERROR(INDEX({Q120}, MATCH([Task Name]$1, {FacilityQ120}, 0)), “Not a number!”) 

I have the same issue when I try to total up the number of items that have a date-it comes back #DATE EXPECTED. I know I could create reports, but there has got to be a way to bypass the column types for a single cell. it would make it easier on my users to not have to go back and forth between reports and sheets to find the totals.

More context on the $1 if beneficial:

•The Task Name $1 is where I enter the facility name on a master sheet. All the other data references that name and matches it to various entries on various sheets. It allows me to update one sheet and all other sheets are updated.

•here is a sample of the working formula. For the GM name, I have this formula. It looks in the current sheet in Task Name row 1 where I type the name of the facility and matches it to the name in the GM column on my one source of truth that matches the same facility name in the HBF column.

•=(INDEX({GMQ120}, MATCH([Task Name]$1, {HBF}, 0)))

Thanks in advance,

-EEK

EEK

I was able to play around with AI and get

=IFERROR(INDEX({Q120}, MATCH([Task Name]$1, {HBF}, 0)) + "", "Not a number!")

But it returns the time as well: 11/06/24 4:59 PM

EEK

Woot! it worked for #DATE EXPECTED. Still working on the date time above for a text column type

=IFERROR(COUNTIF([Audit Summary Sent (date sent)]2:[Audit Summary Sent (date sent)]41, IFERROR(@ )) + "", "Not a date!")

Paul Newcome

The IFERROR is not necessary for (and won't work on) an invalid column value when trying to pull a date value into a text/number column. To do this, you need to convert the date value into a text string by "adding nothing".

=INDEX(……., MATCH(……)) + ""

(plus quote quote)

EEK

Thank you! that seems very promising and more universal. It came back#INCORRECT ARGUMENT SET

=IFERROR(INDEX({Q120}, MATCH([Task Name]$1, {HBF}, 0)) + "")

Paul Newcome

Right. You wouldn't use the IFERROR. If you still want to use it, you need to have an output for it if the INDEX/MATCH is throwing an error.

EEK

Brilliant! Thanks for reminding me what you already told me ;)