A little help for a newbie

D

DeanL

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...
 
O

OssieMac

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.
 
K

Ken

Dean
That sound like an application for a Pivot Table. They are a little
hard to explain, but, it would probably be worth your time to look
into them; rather than jump into writing a macro.
Good luck
Ken
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top