Sum cells in different sheets - do not update relative location if cell moves

[redleg77]

Background:

I am trying to build a rough what-if tool for team resource planning. I have separate sheets for different projects. I have a summary sheet that has totals. I can sum cells for a given resource across the sheets to show all planned work by person for a given time period, however if I move the planned work for a person from one cell to another (one month to another) the sum formula on my summary sheet updates. I've tried absolute cell references, using Index and Indirect (but since I'm going across 5+ worksheets, I don't think these are the right solutions).

Data set
Sheets (Summary, Project1, Project2, Project3, Project4, Project5)
Columns (Month 1, Month 2, Month 3... Month 14)
Rows (Resource 1, Resource 2, Resource 3)

Ideally, I could highlight the hours for any - or all - resources on a given project sheet and drag to later in the year, and the summary sheet would update to reflect the change in hours to the new month (moving 40 hours of resource 1's time from Month 2 to Month 4, results in Summary for Month 2 decreasing by 40 hours and Month 4 increasing).

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...t-update-relative-location-if-cell-moves.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.