Data sharing

[Guest]

Hello All:

Am perplexed about how to handle something, and hoping someone can advise.
Running Excel 2003, and I am not extremely strong on formula building.

Worksheet One: Table of orders to various accounts (Column A is account
code.)

A B C D E F
CGC Ship To Address City Qty Model
1 D1EFM J.Smith 123 Main Dallas 1 Widget A
2 W1E K. Reed 456 Long Houston 2 Widget B
3 D1EFM J. Smith 123 Main Dallas 1 Widget A
4 U3A T. Jones 987 3rd Springfield 3
Widget C
5 D1EFM J. Smith 123 Main Dallas 1
Widget A

Multiple users update this worksheet, but in order to provide a summary
worksheet by customer code, I would like a separate file to auto update data
from this worksheet, only if CGC (Column A) = "D1EFM". I do not wish to sort
my original table. Any advice on how to best accomplish this, would be
greatly appreciated.

TIA,
Sandi

 

[Dave Peterson]

Personally, I'd try to keep all my data in that one worksheet. Maybe you can
use data|filter|autofilter to show/hide the rows you want shown or hidden.

If that fails, maybe sorting wouldn't be bad--add an extra column that can be
used to resort to get back to the original order (just put 1, 2, 3, ... down the
column). Sort the data by any field and resort by this helper column when
you're ready.

Or depending on what kind of summary reports you want, take a little time
learning about data|Pivottables. You can do some very nice things if you need
numeric summaries.

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

===========
But if you absolutely must put your data into separate worksheets...

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm