INDEX(COLLECT Returning #Invalid Value

05/04/21
Answered - Pending Review

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

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

  • edited 05/04/21

    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)

Sign In or Register to comment.