Can the Collect Function be used with a Column reference

Jason Hidek

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

Jason Hidek

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 !

Kelly Moore

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

Jason Hidek

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 !