Excel Database

[Guest]

I was approached today by one of the VP's of the company I work for. He would
like to be able to enter information and then have the information sent to
where it needs to go.

For Example:

One person would enter

Salesman; Company Sold To; State; Pairs sold.

So Column A would be the salesman;
Column B would be the Company Sold To
Column C would be the State the company sold to is in
Column D would be the Amount of Pairs sold to the company.

Then after all that information is entered; He wants to be able to see the
percentage of the salesman's sales, in a given state.

So let's have it sort by the state; The salesman is assigned 6 states. I can
sort by the state, that's not a problem. I am just wondering if this would be
easiest in Excel or another program, access or something.

And If I do it in excel; I'm not very familiar w/ macro's. So if you may be
able to point me somewhere for Macro Knowledge; it would be appreciative.

Thank you.

 

[Guest]

Once you do this, chances are the data analysis will change, i.e., he'll want
more detail, better refinement, or new fields.

You would be better off in MS-Access. Take the time to plan the database,
the tables, and the data "normalization". You will be glad you did.

Plus, Access will have better reporting capability.

Question? It surprises me that these sales are not being entered somewhere
else, and that application should be capable of making these "reports". If
so, you can either put a 'hook' into that database with Excel, or a
third-party report writer, to get this done. This would eliminate the
requirement for Excel or Access. You can do the extraction of the data to
MS-Access, too.

P.

 

[Guest]

Well Our company software does; Although, we only want to track future orders
that won't be entered into the company software, only because it can't really
keep track of what is future and what is at once orders.

So that is why we don't do that now, or your right, we would have that
ability.

But trying to save money, this is what you get, extra work when it shouldn't
be needed.

 

[Guest]

Well; I've decided to do this in excel; Only because I am more familiar w/
excel than access.

Is there an easy way to illustrate the percentages each state has bought
according to pairs?

 

[Guest]

This really is a database application, so set Excel up to "roll-up" the data
into an unused portion of the worksheet (use the DATA functions) based on
your criterion. Then, do your calcs there. You could produce a graph, too.
You could use Data Form for the data entry to speed the process and prevent
errors.

You can learn MS-Access quickly for this application (no complex tables and
relationships). If I were you, I would get a book, or run a tutorial (Help,
or MS-Office Web site).

re: Your existing application - In place of future orders, can you enter
quotes or estimates (like in job costing)? You could manage the future orders
there, then convert them to an order at the time of sale.

P.

 

[Guest]

re: Your existing application - In place of future orders, can you enter

quotes or estimates (like in job costing)? You could manage the future orders
there, then convert them to an order at the time of sale.

your right, we can; But we would have to pay someone to create that report.
It would be using Crystal Reports; not sure if you've heard of it or not. But
that would be more costly than just doing this excercise in excel.

I'm not very familiar w/ the function "DATA".

But I believe I have already made the spreadsheet in excel; it's pretty
plain. But it serves it's purpose.

Thank you though.

 

[Guest]

I have the spreadsheet setup; Now I'm wondering about graphs and stuff.

The data entry part is on Sheet 1; The states are sorted on Sheet 2; the
Salesman on Sheet 3.

I would like to know how to make a graph, for Sheet 2 and Sheet 3, based on
the corresponding information. Such as on Sheet 3, I would love to see a
graph, that shows percentage of the the sales of the salesman, meaning if
salesman 1 sold 265 prs, and salesman 2 sold 345 prs. Add 265 & 345 to get
610; so, Salesman 1 sold 43% of total sales, and salesman 2 sold 57% of total
sales.

Sheet 2 I would love the same thing, only since it's the state one, have the
graph show which state sold what percentage of total sales.

 

[Guest]

It sounds like you set this up like a relational database, i.e., multiple
tables (sheets). Excel works as a flat-file database (one file/table,
multiple fields and records). You can use lookup functions on Excel to help
with data entry, e.g., salesperson number produces name in another column.
You can use the data form to produce a data entry screen in field order.

You can produce an area in the spreadsheet to summarize data and act on it.
Look this over:
http://office.microsoft.com/en-us/assistance/HA012186941033.aspx

Personally, I would use Crystal Reports (has a better report writer), or
Excel using Microsoft Query. Look here:
http://office.microsoft.com/en-us/assistance/HA010864661033.aspx

P.

 

[Guest]

The Data Form helps.. for other people entering the information.

I would love to be able to use Crystal Reports to create my own thing; But I
don't know the first thing to it.

I appreciate your time. I am going to keep this spreadsheet simple. It does
what I need it to do. I always have a habit of making things better, but I'm
done. It's over, and it works. Always the best part.

Thanks again,
Jeremy