Formula returning "invalid column value", but it looks correct.

BethWork

I used the AI formula generator in Smartsheets and I'm still getting an invalid column value error.

I can't figure out why because there is data in the column.

If the IMP/DISC column displays Implementation then I want the value from the Implementation Status Update Date to show.

If the IMP/DISC column displays Discovery then I want the value from the Disocvery Status Update Date to show.

Below is the formula I use. It only shows an error for Discovery and not for implementation. What is wrong with the formula?

=IF([IMP/DISC]@row = "Discovery", [Discovery Status Update Date]@row, IF([IMP/DISC]@row = "Implementation", [Implementation Status Update Date]@row, ""))

DKazatsky2

Hi @BethWork,

I suspect the #INVALID COLUMN VALUE is coming from the part of the formula that is evaluating  IF([IMP/DISC]@row = "Implementation", [Implementation Status Update Date]@row, "") - since the implementation date has #NO MATCH, it is causing the failure even though that condition is not met.

Try wrapping the formula used in the "Implementation Status Update Date" column inside an IFERROR function and return "".

Hope this helps,

Dave

BethWork

HI @DKazatsky2

I don't know what you mean by wrapping it in IFERROR. Is this what you mean?

ORIGINAL:

=IF([IMP/DISC]@row = "Discovery", [Discovery Status Update Date]@row, IF([IMP/DISC]@row = "Implementation", [Implementation Status Update Date]@row, ""))

WRAPPED:

=IFERROR(IF([IMP/DISC]@row = "Discovery", [Discovery Status Update Date]@row, IF([IMP/DISC]@row = "Implementation", [Implementation Status Update Date]@row, "N/A")), [Discovery Status Update Date]@row)

DKazatsky2

@BethWork,

I am referring to the formula that gets the date in the "Implementation Status Update Date" column. Is there a VLOOKUP formula there? If so, that is the formula to wrap.

BethWork

@DKazatsky2

I'm sorry, I don't understand what you mean. I don't have a formula pulling in the date in the Implementation Status Update Date columns. Is that what you're referencing?

DKazatsky2

@BethWork,

Yes, that is what I was referencing. The image you shared shows that column has a formula so I thought it may have a VLOOKUP which was returning #NO MATCH. If that is not the culprit, I am not sure what else to look at here.

Hopefully some others can provide some thoughts as well.

Genevieve P.

Hiya! Just jumping in here to confirm that it will be the NO MATCH error that's causing this other formula to error as well.

@BethWork these are the errors we're referencing:

Based on the fx in the column name, it looks like these dates are being populated by a formula. This is the formula that should have an IFERROR wrapped around it, so if there's no match you can return either a blank cell or a text of your choosing:

=IFERROR(formula, "No Match")

Cheers,

Genevieve

BethWork

@Genevieve P.

Ah, I see what you mean. The formula for that column is below. Where do I insert the error so I wrap it?

=INDEX({Implementation Summary Sheet Range 1}, MATCH([Implementation Project Number]@row, {Implementation Summary Sheet Range 2}, 0)!)

Genevieve P.

Hi @BethWork

Thank you! Yes, this is exactly what we were looking for. Wrap your INDEX(MATCH in an IFERROR and your other error should resolve as well. 🙂

=IFERROR(INDEX({Implementation Summary Sheet Range 1}, MATCH([Implementation Project Number]@row, {Implementation Summary Sheet Range 2}, 0)), "")

Cheers,

Genevieve

BethWork

@Genevieve P. I applied that formula to the column and it works in the sense that it now displays a blank cell, but it doesn't resolve my original issue. I still have an invalid column value error specifically for Discovery.

What else am I missing?

Genevieve P.

Hey @BethWork

Another reason for the error could be if the columns are different types. Can you confirm that all your date columns are set up as Date type of columns? (Even ones with formulas)

BethWork

Hello @Genevieve P.

AH! That was it! That was actually the issue. The IMP/DISC column wasn't set to a date type column. Thank you! I forgot about that part. I appreciate your help with this.

BethWork

Hello @Genevieve P. ,

I have a similar question for you. I'm in the same sheet and using a similar formula, but for a symbol column.

In the Project Overall column I used the following formula and made sure that the column type is set to symbol (red, green, yellow, and gray).

=IF([IMP/DISC]@row = "Discovery", [IMP Overall]@row, IF([IMP/DISC]@row = "Implementation", [DISC Overall]@row, ""))

For some reason, it's not pulling in the gray symbols from the DISC Overall. Is there something else I'm missing?

Genevieve P.

Hi @BethWork

Since it's returning a blank cell, the formula is not finding a match for what you're looking for:

=IF([IMP/DISC]@row = "Discovery", [IMP Overall]@row, IF([IMP/DISC]@row = "Implementation", [DISC Overall]@row, ""))

Can you double check that the IMP/DISC column states "Implementation", spelled letter-for-letter the same?

BethWork

Hi @Genevieve P.

Once again, I did something silly. I had the columns reversed in my formula. I had Discovery matching to IMP Overall and Implementation matching to DISC Overall. I appreciate your help again.

Genevieve P.

No problem! I'm glad I could help.