Need Help with Automating Excel Spreadsheet

[bayuga]

Hello,
I have a spreadsheet with about 10 worksheets and 1 is the master list sheet. The other 9 sheets are by managers (let's call these sheets manager 1 - 9 sheet )which is used to make changes or additions to data.

My question is, how can I make the spreadsheet to where when one goes into the manager sheets and makes changes, the master list sheet will capture all changes or updates from the manager sheets automatically. One other thing. The master list needs to be protected so that noone can accidentally make changes in this sheet.

 

[MacroAlan]

If the other sheets are formatted the same, you can bring the numbers forward to the Master with something like:

=SUM(Sheet2:Sheet9!A2)

.

What do the other sheets contain? The same layout? Differnt?

Alan

It is easier to apologize than ask permission.

 

[AnotherHiggins]

I would suggest that you are doing things backwards.

All similar data should be kept together on a single sheet - never broken down by manager, month or week, employee, etc.

What you should have is a single table of normalized data and then different sheets in which you pull relevant data from the master list. This is a much, much easier structure to maintain.

On your main sheet, just add a column that has the managers names. This way you can slice and dice your data any way you want....

Want to see data for a single manager like you have it now?
[tab]No problem
Want to see data for all managers, as you can now?
[tab]No problem

[attn]But wait, there's more!![/attn] If you have your data stored properly you can also, very easily, see it in any other way:

Want to see 3rd Quarter data for all managers?
[tab]No problem
Want to see data for East Region broken out by weeks?
[tab]No problem


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[bayuga]

Alan,
Thanks for the info. The manager sheets does have all the information the master sheet has. The only reason why the there is even is master sheet is so that we can view all data in one sheet. I tried the code and did not work for me.

Anotherhiggins,
I am not quite sure how to do what you are proposing. Is there a code I need to do.

 

[SkipVought]

bayuga,

So how much different are these answers than the one you got in thread707-1324005?

John is referring to exactly what I suggested.

There is no code required. You can use the PivotTable Wizard, for instance.


Skip,

_[red][/red]

 

[AnotherHiggins]

not so much a code/formula as a complete redesign of your data.

Example format:
[tt]
A B C D
Manager Date Sales Other
Smith 1/1/07 1.3 XX
Smith 1/2/07 2.4 YY
Jones 1/1/07 1.5 ZZ[/tt]

Obviously I don't know what fields you have, but the key is to have the manager as a column, and to have the data for all the managers together in one sheet.

What I would do is this:

- Make a copy of your file in case something doesn't go as planned.
- Insert a new column into each of the 9 sheets
- Fill this new column with the name of the manager for that sheet
- Copy 'n' Paste all of the data into one single sheet, making sure to only copy the header row once

Now you have a single table. Some tools you might want to fiddle with to pull the data out by manager (or by other groupings):
- VLookUp
- Pivot Table
- AutoFilter

Search for each of those in Excel's help file and here on Tek-Tips. If you have questions about a particular formula, I'd suggest starting a new thread for each.

Post back here for any further questions about getting your data normalized. If you do need further help, please list the fields (columns) you have and some example data.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[ChanTheManCan]

I hope this help...It works like a charm for me.

From Bill Jelen's Learn Excel from Mr Excel......

"Copy Your Pivot Table For Every Customer

Szilvia Juhasz of Los Angeles-based XSil Consulting passed along this excellent trick for pivot tables. Create a pivot table with customer as a page field and any other combination of fields in the pivot table. Make sure to use File – Page Setup to set the margins, header, footer, etc., because you are about to make 50 copies of this worksheet in a split second.

From the Pivot Table toolbar, open the Pivot Table dropdown. At the bottom of the dropdown, choose Show Pages.

In the Show Pages dialog, Excel will list all of the fields in the Page area of the pivot table. In this case, it is just the customer field. If you have more than one field, you must select one of the fields and click OK. In less than a second, Excel will replicate the worksheet for every value in that field. In the current example, Excel created 50 new worksheets. Each worksheet shows only the data for one particular customer. "