Dispalcement, Vlookup problems.

[Paul987]

Hello all -

I am trying to set up a spreadsheet that will track the performance of
several people selling several different products. Each day, it will
track the number of a certain product sold, and the profit, for many
products. In the end, I would like to be able to analyze each salesman
by product, against the group, for a date range etc....

In column A I have the date
In column B I have profits product 1
In column C I have # sold Product 1
In column D I have profits product 2
In column E I have # sold Product 2

Trying to explain this the best I can...
On a seperate sheet"Overview", I have a list of all the products. I
would like to be able to enter the tab name in a cell and specify a
date range, and have the results for those products populate on this
"overview" tab. I'm having a problem with the vlookup and the
conditional sum, formulas. Any help you could offer would be great. I
will check back often to give details if necessary.
Thanks much,
Paul

 

[DOR]

Paul,

Since you are setting up the spreadsheet now, I advise you to not use
different tabs for different Salespersons, if that is what your
different tabs are. Put all of your data in one sheet, with columns
for Salesperson's ID or name, Date of Sale, Product ID, # Sold, and
Profit. Then you can easily analyze or summarize the data using a
Pivot table or basic formulas in another sheet. Spreading the same
data type across different sheets is a PITA and I would advise strongly
against it. It is also generally inadvisable, to put the same data
types (like product sales or profits) in different columns, and
particularly separated columns, when you want to summarize across them,
although it doesn't give you nearly the same grief as different tabs.

You may have to use a look up against a master list of products, which
should be on a different sheet, to get the profits per unit sale, but
that isn't all bad, since you may use the product ID column in that
master table as a data validation column for entering the Product ID in
the Sales sheet.

Let me know if you would like an example or send or post your
spreadsheet and I will send an example.

Hope this helps,

Declan O'R

 

[Paul987]

Declan -
Thanks for the advice. I switched things around a little bit
accordingly. I'd rather do it right the first time, I just thought
having seperate tabs for salespeople would make it easier to add/remove
people and products. I've attached the sheet, note that it has two
tabs. I'm having probs envisioning the formula involving both the
lookup for the correct date range, and correct product for a specific
salesman. any help you can provide is greatly appreciated

Paul.


+-------------------------------------------------------------------+
|Filename: SalesData.txt |
|Download: http://www.excelforum.com/attachment.php?postid=4076 |
+-------------------------------------------------------------------+

 

[DOR]

I am uploading with this message a sample file based on your data.

I defined dynamic ranges to make the formulas reponsive to additions to
new sales. Just add the sales beneath the samples shown in the Sales
sheet and the formulas will find them.

I put three sales summaries on the sales summary sheet. Each sales
summary covers a date range defined in the top left hand corner.
Examine these to see how you would do your own sales summaries, which
you might put on different sheets depending on your desires.

I added a sample pivot table on another sheet, but I am not a big fan
of pivot tables for on-going analysis since they are not automatically
updated with new data; you have to refresh them or have a macro do so.
I also do not like their imposed format, but they can be extremely
useful to people who have difficulty with summarization formulas or who
want a quick one-time analysis.

Hope this helps

Declan O'R


+-------------------------------------------------------------------+
|Filename: SalesData_DOR.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4077 |
+-------------------------------------------------------------------+

 

[Paul987]

Declan -
Using Pivot Tables, I was able to organize the info in the way
needed, once it was put on one tab. Thanks for the advice and help, m
problem is now solved.

Pau

 

[DOR]

An additional comment - I assumed that you could have a large number o
expanding number of products. If you have a small number of product
and you don't tend to add new products,it is possible that a layou
with two columns (sales and profit) per product might be more usabl
than the approach I suggested. That is really up to you to assess.

Good luck in you effort. If you have any questions, let me know.

Declan O'