"#invalid column value" Error in Sheet Summary

Dennis92

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

MVP OPS

Hi @Dennis92

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

Dennis92

Hello,

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

MVP OPS

Hi Dennis,

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

Dennis92

Hi Brad,

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

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.

Dennis92

Hey Brad,

thanks for your effort :)

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

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.

Dennis92

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

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.

Dennis92

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

That's great Dennis,

Happy to help! Glad you were able to resolve.

Lbreitbach

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