INDEX(COLLECT Returning #Invalid Value

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

Find more posts tagged with

Accepted answers

All comments

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.