Dynamically choose what column to sum if in another sheet

LouizA

I am trying to build out a scheduling tool for my team. On one sheet (source), they can add all the jobs/staffing/hours. The other sheet (total) I want to sum total hours by week for each team member. I could create a Sum Collect and do a separate range for each week. However, I have the week # on both sheet, so am hoping there is a more efficient way to look up what column to sum if in. I have attached a snip of both sheets for reference.

Source Schedule.png

Total.png

This is the formula I currently have but I really don't want to make 52 different ranges/formulas ("{Import Template - ProStaff Range 1}" is the range I'd like to replace w/some sort of index match or dynamic look up function):

=SUM(COLLECT({Import Template - ProStaff Range 1}, {Import Template - ProStaff Staff}, $[Staff Name]@row)).

Will.Parente

Depending on what plan you have you might have a tool that can solve this for you:

https://www.smartsheet.com/content-center/product-insights/product-updates/new-workload-tracking

Hopefully that is the easy fix!