INDEX/COLLECT Giving #INVALID VALUE

Colin B

I have a sheet set up with a form for employees to enter their name, hours, and week ending date on a weekly basis. In order to create a dashboard chart to compare all employee's hours week-to-week, I've set up a separate sheet to reference the entry sheet with the week ending dates as the primary column and employee names as column headers. To pull the hours into the new sheet I've created the following formula:

=INDEX(COLLECT({Actual Utilization - Weekly Utilization}, {Actual Utilization - Name}, "Employee Name", {Actual Utilization - Week Ending}, [Week Ending]@row), 1)

In theory this should pull the hours from the row where the given employee name and week ending match the respective values in the source sheet, however it is giving an #INVAID VALUE error. Can anyone see what I'm doing wrong?

EDIT: Solved it, week ending dates needed to both be Date column types. Silly mistake.

Melissa Boehl

Dates must be in a column that is set as a date type column. It cannot be the primary.

Melissa Boehl

Dates must be in a column that is set as a date type column. It cannot be the primary.