Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data sharing
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data sharing
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/assistan...lconPT101.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 RUSH2CROCHET wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
ranking query | Excel Discussion (Misc queries) | |||
Sharing data across worksheets within a workbook based on identifi | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |