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 18, bring in another rangecriteria 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
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!