Hi guys,

I'm fairly fluent in MS Access but I've been handed a task that is
baffling me. I've been asked to produce a report(s) in Excel that has
a series of sales by territory. I have a bunch of raw data in a
spreadsheet that has several items including Name, Address, State,
ZIP, Territory, ItemCode, SaleDate, Quantity and Price. From this
data, I would like to create a macro or something that would take the
data for a given territory and "dump" it into a workbook to track
sales performance over the past 6 months.

Apart from the most simple use, I've never used Excel in this way and
I'm currently pulling my hair out so I'd be so grateful if anyone
could help or point me in the right direction.

Many thanks, Dean...

Hi Dean,

Try Autofilter. Look it up in Help. It places drop down arrows on each of
the column headers and allows you to select just the data that you want. A
bit like having a listbox on every column of data.

You can copy the visible cells and paste them to another worksheet in the
same workbook or in another workbook.

If you want to create macros, to get the syntax you can record the macros.
They often require editing but it is a good start and if you are already
familiar with Access then you should not have too much of a problem.

Also, lookup Subtotal function. You can do a lot with it when summing,
counting averaging etc in conjunction with Autofilter.

I find that by placing the column headers for the data about the 5 or 6 rows
from the top of the worksheet and then use Freeze panes so that the column
headers remain on the worksheet (do not scroll with other data). You can
place the subtotal formulas above the column headers and then both the column
headers and the results of the formulas are always visible.

--
Regards,

OssieMac


"DeanL" wrote:

Hi guys,

I'm fairly fluent in MS Access but I've been handed a task that is
baffling me. I've been asked to produce a report(s) in Excel that has
a series of sales by territory. I have a bunch of raw data in a
spreadsheet that has several items including Name, Address, State,
ZIP, Territory, ItemCode, SaleDate, Quantity and Price. From this
data, I would like to create a macro or something that would take the
data for a given territory and "dump" it into a workbook to track
sales performance over the past 6 months.

Apart from the most simple use, I've never used Excel in this way and
I'm currently pulling my hair out so I'd be so grateful if anyone
could help or point me in the right direction.

Many thanks, Dean...