INDEX(COLLECT Returning #Invalid Value

Trillian Schroeder

Hello,

I am getting #invalid value witht he following formula:

=INDEX(COLLECT({Purchase Quantity}, {DepartmentConsolidation}, "112", {Consumables}, $[Product Description]@row, {Purchase Date}, >=[Week Start Date]@row, {Purchase Date}, <=[Week End Date]@row), 1)

There is definitely a match on the sheet and if I remove " {DepartmentConsolidation}, "112"" from the formula it works. Ayhelp would be most appreciated

Paul Newcome

Double check the source data in the {DepartmentConsolidation} range to see if that error is present in any cell.

Trillian Schroeder

Thanks Paul,

The error is not present in any cell. What's weird is I am using essentially the exact same formula in a separate sheet which is working perfectly. The only discrepancy is that the reference is for {Department} a separate column, that formula is as follows

=IFERROR(INDEX(COLLECT({Purchase Quantity}, {Consumables}, $[Product Description]@row, {Department}, "Eureka-Manufacturing", {Purchase Date}, >=[Week Start Date]251, {Purchase Date}, <=[Week End Date]251), 1), " ")

if I change the formula in the sheet I am working on to match it except changing {Department} to "112-Warehouse" I get #INCORRECT ARGUMENT:

=INDEX(COLLECT({Purchase Quantity}, {Consumables}, $[Product Description]@row, {Department}, "112-Warehouse", {Purchase Date}, >=[Week Start Date]@row, {Purchase Date}, <=[Week End Date]@row), 1)