"#invalid column value" Error in Sheet Summary

Hello all,

I would like to retrieve the date of "LR1" with the following Index Match function (see field 18). Once I hit enter I receive the error message "#invalid column value". Is something wrong with my formular?:

=INDEX(Finish:Finish, MATCH([Field 16]#, [Task Name]:[Task Name], 0))

Many thanks in advance for any help here,

Dennis


Answers

  • MVP OPS
    MVP OPS ✭✭✭✭✭

    Hi @Dennis92

    formula looks good. Is your field 18 a date field?

    best,

    Brad

    www.MVPOPS.com

  • Hello,

    Thanks for your quick reply. Yes, field 18 is a date field.

  • MVP OPS
    MVP OPS ✭✭✭✭✭

    Hi Dennis,

    In your sheet do you have a value in your finish column that is not a date possibly?

    best,

    Brad

    www.MVPOPS.com

  • Hi Brad,

    I checked the Finish column. Every entry is a date and the column property is also a "Date" Type.

  • MVP OPS
    MVP OPS ✭✭✭✭✭

    Thanks Dennis,

    I recreated and this does look to be a smartsheet limitation, regarding referring a cell within sheet summary formula in a sheet summary. When I substituted the actual value it works fine. You might reach out to smartsheet support to verify. Hope this helps.


    best,

    Brad

    www.MVPOPS.com

  • Hey Brad,

    thanks for your effort :)

    What do you mean by "When I substituted the actual value it works fine."

  • MVP OPS
    MVP OPS ✭✭✭✭✭

    Hi Dennis,

    In the sheet summary index match formula I input the value "LR1" as the match criteria and it returned the correct result.

    Its a work around if you always are inputting the value you need in the formula itself.

    best,

    Brad

    www.MVPOPS.com

  • Hi Brad,

    like this? =INDEX(Finish:Finish, MATCH("LR1", [Task Name]:[Task Name], 0))

    This formula still doesn´t work for me. Same error message appears.

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    edited 06/04/22

    Hi @Dennis92 ,

    If you are getting the invalid column value still, double check that your column and field properties are correct. When I look at your first screen shot I don't see the blue calendar icon in the field. More than likely its set as a text/number field in the sheet summary which could be giving your the error. I have attached two screen shots below of my sheet to recreate. Hope this helps.


    best,

    Brad

    www.MVPOPS.com

  • Thanks for your help Brad!


    In the end I wanted to have an equasion here that substracts two dates and devides it by 7 to receive the amount of weeks in between. This formula works now:

    =(INDEX(Finish:Finish, MATCH("FDR", [Task Name]:[Task Name], 0)) - INDEX(Finish:Finish, MATCH("IDR", [Task Name]:[Task Name], 0))) / 7

  • MVP OPS
    MVP OPS ✭✭✭✭✭

    That's great Dennis,

    Happy to help! Glad you were able to resolve.

    best,

    Brad

    www.MVPOPS.com

  • I had this issue awhile ago and I found something that solved it for me ( I just forgot how to do it). Weither you use index match or vlookup to pull date in you need to add =iferror( vlookup or index match here)+"","-")

    so it should look like this =iferror(INDEX(Finish:Finish, MATCH([Field 16]#, [Task Name]:[Task Name], 0))+"","-")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!