Excel 2007 : Master workbook needs to auto transfer data to other spreadsheets

**MDMagura** · 09-20-2010, 05:19 AM

I have a master workbook that I enter all of my gross sales on. There are three categories of sales, eg. MDU, D2D, and BUS. These three codes are found in Column A of the master before any other data is entered into a row for that sale (name of contact, address, type of product sold, etc).

For each of the three categories of sales, there is a corresponding worbook. I need each of these workbooks to be automatically updated when a new sale is entered into the master. How do i tell excel to do this?!?!?!??! IF function?? VLOOKUP??

I know nothing about VB or VBA.

Thank you for any input.

**MDMagura** · 09-20-2010, 05:32 AM

A .........B................C................ D................... E
Code Name Date of Sale Address Product Sold

1 MDU Bill 9-15-10 Lake Ln P3
2 BUS Anne 9-15-10 Sun Dr P1
3 D2D Pete 9-16-10 Hill Ct P3
4 BUS Larry 9-15-10 Gull Ln P2
5 D2D Sal 9-14-10 Sand ln P3

Above is an example of my master spreadsheet. I need to create a formula that will tell excel to copy B1 through E1 to the MDU workbook based on the code in A1 and the info from B2 through E2 to the BUS workbook, and continiung down coumn A based upon the specific code. This master sheet is updated daily.

HOW DO I DO THIS~!?

**royUK** · 09-20-2010, 05:32 AM

I really can't see why you need to do this. A properly laid out table to contain the data would allow you to use a pivottable which wouldbe much more efficient

**MDMagura** · 09-20-2010, 05:43 AM

I am no expert in pivot tables... what's the best way to set this up? This whole database is new so I need to find the best way to set it up for when I start inputting data.

**royUK** · 09-20-2010, 05:46 AM

It depends which version of excel that you are using and the data layout. The best way to learn is to just experiment with creating a Pivottable.

You can attach a dummy workbook

**MDMagura** · 09-20-2010, 04:58 PM

Is it possible to

A. Set-up a pivot table that only gathers certain rows based on the content of Column A? Eg. I have the three different types of sales and want each sale to be entered onto it's corresponding pivot table. If so, How do i manipulate this??

B. Can I have a pivot table that automatically updates as new info is added to the master sheet (the sheet where the info for the pivot table is gathered)

Please advise. Thanks.