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

Options
BethWork
BethWork ✭✭✭✭
edited 01/05/24 in Formulas and Functions

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, ""))



Tags:

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭
    Options

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

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

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

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

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

    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

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

  • BethWork
    BethWork ✭✭✭✭
    Options

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

    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

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

  • BethWork
    BethWork ✭✭✭✭
    Options

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

    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)

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

  • BethWork
    BethWork ✭✭✭✭
    Options

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

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

    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?

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

  • BethWork
    BethWork ✭✭✭✭
    Options

    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.
    Genevieve P. Employee
    edited 01/08/24
    Options

    No problem! I'm glad I could help.

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!