Can the Collect Function be used with a Column reference
I have used the index and collect function successfully when using just a range in the formulation. But is seems like if I try to reference the whole column either as the range or the criteria, then I get the #unparseable value.
Reason being that when new rows are added, I want this as a Column formula since the new rows will increase the range, thus would have to be manual copied down.
I have three columns: CAT is the category value that we want to find; CM is the Current Month (as a number) as one criteria and the other criteria column is MATERIAL.
So find the material based on a month and return the value from the Category field. Here is how it works when I just highlight the range of the current rows:
=INDEX(COLLECT(Cat1:Cat8, Material1:Material8, Material@row, CM$:CM$, PM@row), 1)
So it works on my 8 row test as it takes the material # from the current row, and looks back to the previous 7 rows and sees if it finds it based on the month we tell it from another colums. So bascially we are capturing the previous value of the current material by looking back up the list.
However, if I convert any of those ranges to reference the full column, the formula breaks
Answers
-
Hey @Jason Hidek
Yes, Collect functions work with entire column ranges. Like most functions, the ranges must be equal length, so to try to use entire columns with some criteria but only 8 rows of another will cause problems.
For the above, your formula would become
=INDEX(COLLECT(Cat:Cat, Material:Material, Material@row, CM:CM, PM@row), 1)
If there is something special about rows 1-8, bring in another range-criteria pair that will further delineate only that dataset
Does this help?
Kelly
-
Yes, it did, not sure why every time I wrote it, it didnt work, probably the way I wrote it or used brackets, etc...
Thanks !
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!