Why is Index Collect formula returning Invalid Value error?
I am trying to pull a project number from a source sheet into my current sheet based on the employee number and date column matching on both the current sheet and source sheet. Here's my current formula:
=INDEX(COLLECT({Project # - MP Proj}, {Employee # - MP Proj}, [Employee ID]@row, {Week Start Date - MP Proj}, [Pay Period Helper]@row), 1)
This returns the #INVALID VALUE error.
The formula works as such: =INDEX(COLLECT({Project # - MP Proj}, {Employee # - MP Proj}, [Employee ID]@row), 1)
However, this only pulls the first value in which the employee #s match and does not account for the dates. Looking at the source sheet and current sheet, there are rows in which both the employee # and week start date show the exact same value as on my current sheet.
Any ideas what might be going on here, and how to fix it?
Best Answer
-
In all likelihood your [pay period helper] column isn't a date field.
Answers
-
In all likelihood your [pay period helper] column isn't a date field.
-
The pay period helper was. The column in the source sheet was not for whatever reason. It is pulling correctly now.
Thanks!
-
Ah, makes sense... pleased you got it sorted.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!