Dynamically choose what column to sum if in another sheet

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)).

Find more posts tagged with

Accepted answers

All comments

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!

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.