"#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
-
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.
-
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.
-
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."
-
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.
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 214 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 456 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!