This is my first post to this forum, so I hope I don't sound too noob-ish/pathetic. I have created a resource planning xls that has multiple sheets. Here's the basic stucture: Sheet 1 is a summary sheet that sums up cells between two placeholder sheets named "0" and "1". The sheets in between are distinct projects where I have estimated hour allocations (Column A=Staff Name, Row 1=date).
So the summary sheet, through brute force, adds up each persons hours for all dates. For cell I5 in the summary I have:
It generally works fine, but I have two concerns:
1) When I insert a new sheet the summary doesn't recalculate to include the new values.
2) I want to be able to see which sheets are contributing to a particular cell. (eg. what projects a particular person is working on in a particular day)
I feel like there is a more elegant way to do this with pivot tables, but I'm not sure how to get one pivot table to use data from multiple sheets.
Any suggestions? All help is greatly appreciated.
Bookmarks