Colin B
Colin B ✭✭
edited 08/10/22 in Formulas and Functions

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.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!