Multiple Sheets that need to update off master sheet HELP!

This has sat for quite a time with no replies... So, (at the risk of exposing my ignorance) can I make some suggestions/ask some questions...

What is the difference between the data on the individual employee sheets and the master sheet? The master sheet seems to be EXACTLY the same as the individual sheets, except that everyone is there, one after the other. Do you really need all of the data on the Master sheet?

The formatting of your data makes it very difficult to come up with a future-proof solution. You're going to be adding rows here and there all the time. that'll make setting formulae a nightmare.

So... do you really need the master sheet to be a simple regurgitation of what's on the individual sheets, or is some level of summary possible/workable? Also, can we muck around with your data layout to make it easier to work with (see a possible alternative on sheet Employee 1)?

OK. I'll be back to you in the UK morning, assuming no-one else has got you sorted out by then. It's time to eat here....

Excel IS worse than crack cocaine...

I'd try something like this.

On Sheet 2, assemble your list of Employees and product lines. If there are more than 20 employees, copy the formula in the pale green box on Master further down and the same for product lines (if > 20). On the Master sheet, I'd just enter new lines as and when they occur, not worrying about the order in which they're entered.

Normally, I'd use a Pivot Table to do the summaries, but there is a reason why I decided to suggest SUMIF instead. As you add data to the Master List, the formulae on the Employee sheets do their stuff. This can be SLOW. So, I've set the calculation option to MANUAL. Once you've finished updating the Master List, hit F9 to update everything. (if you also had pivot tales you'd have to hit F9 and then refresh the Pivot. It's easier to remember one thing than two...).

To add a new employee, first add their name in Sheet 2 column A. Then right click on the TAB name of any employee and select "move or copy" click "create a copy" and "move to end". Change the name of the newly created sheet to that of the new employee. Then, select the name of the new employee from the dropdown Box (Cell A1) in the newly created sheet, and hit F9 to update and away it goes... Try adding a new employee and add a line or two for them on the Master sheet...

If you like the basic idea, the next step is to think about the summary data needed and protecting cells from being accidentally changed.

Finally, the formulae in the Employee sheets that do the sorting are array formulae. These need to be set using CTRL + SHIFT + ENTER. That will add the curly braces ( { and } ) around the formulas and make them work (don't type them yourself). Finally - don't add/subtract rows on the Master sheet between Row 1 and Row 6. It won't work if you do. If you MUST change them, let me know first & I'll tell you what you need to do...

There will be something I've forgotten to add, so I'm sure you'll find some teething problems. If it hadn't been pouring here today, I might not have done quite as much...

OK, just whenever you get a chance...

Hi. I'm out and about for a while. I'll get back to you ASAP.

A couple of thoughts for you:

Your data on the Employee worksheets will not sort because the values in the cells are drawn from the Master Sheet. If you want the data in order, sort the Master Sheet.
I suggest a slightly different version of the formula on the Employee worksheets. Enter in A4, copy across and down:

Formula:

Please Login or Register  to view this content.

The titles on each of the worksheets in rows 1 and 2 would be better if you didn't Merge and Center. Use format cells, Alignment, Center Across Selection. This will eliminate problems with merged cells.

The next final version. I've taken NDM's suggestions on board and tweaked it a bit further. See what you think... What sort of sales graphs did you have in mind?

Olympiapools, if you want any help with assembing some graphs, do come back and ask. Equally, if you run into any snags with the sheet, just shout.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED, and (preferably) say thanks to all those who helped by clicking the Add Reputation button at the foot of their post(s).

Thanks.

Best to close this one down & start another one...