We help IT Professionals succeed at work.

Pivot table instead of all these formula's

Brent
Brent asked
on
I thought I was pretty clever to come up with this worksheet, but it has turned out to be a nightmere with any changes. The worksheet (which I have worksheet for every month) is used to collect data for staffing. the rows with  all the fomulas are hidden from users. We track if a company (fire trucks with 4 men) is an advance, intermediate or basic life support apparatus (P=advance, I=intermediate, B=basic). We work in 3 24 hours shifts which is called the A,B & C shift (row 3 under the date of month). The city is broken into 6 battalions (geographical boundries) with each battalion having a Chief (supervisor). So that is why you will see E1A, E1B, E1C, ect.

When changes are made (either fire trucks going in service or out of service) it makes it very difficult to report all the percentages. I have tried several ways to place this into 1 worksheet so that I can use a pivot table to collect my data, but have not been successful. I want to keep it easy to use, but also easy for me to report my data.

Any idea on possible layouts would be greatly appreciated. I realize this might be better in a database, but I am stuck using excel for this task.

Thanks
Comment
Watch Question

no attachment
BrentBusiness Intelligence Analyst

Author

Commented:
Wow...that's a lot of data density ;)  I can take a look tonight (after 6:PM EST) when I'll have more time to analyze what you have going on here.  Others may be able to get to it sooner.
BrentBusiness Intelligence Analyst

Author

Commented:
Thank you
BrentBusiness Intelligence Analyst

Author

Commented:
This gives me the data I am after, but it is awkard looking for the worksheet. Plus, when I added more dates, the pivot table did not "grow" to collect the new data. But, it at least gives an idea on what I am trying to do.

thanks
update-als.xls
BrentBusiness Intelligence Analyst

Author

Commented:
I am going into a meeting, so it will be a bit until I am back.

thanks
BrentBusiness Intelligence Analyst

Author

Commented:
I think I'll bag the excel idea and build a small access database. Seems to be the best route. thanks
On analysis, I would wholeheartedly agree - a database is the route to go.  While impressive in scope, the spreadsheet was way too dense to be useable (in an optimal sense).  Good luck with the DB and feel free to post related questions if needed.

Mark
BrentBusiness Intelligence Analyst

Author

Commented:
Thanks
BrentBusiness Intelligence Analyst

Author

Commented:
thanks for the response. Even though I did not use a pivot table, the response and solution is the correct way to go.
feel a tad guilty about accepting points for essentially agreeing with you, but thanks  ;)