Prev: How do I remove the PAGE 1 from an excell worksheet?
Next: Custom formating
From: Charles on 21 May 2010 12:26
I have a sales order spreadsheet that gets updated daily from our ERP system.
The worksheet has the following columns:
1. Customer code (each customer could have several entries per month)
2. Order date (the current worksheet has two years of history in addition to
the daily updates)
3. Amount

What I would like to do is make a new worksheet that has the following
columns:
1. Customer code listed once for each customer
2. Total sales summed by month ( so I would have a column for each 2 years
of history and will add each new month)

I know I could do this with a pivot table but for internal company reasons I
would like to know if anyone can tell me how to do it with a formula.

Thanks in advanced for your help!!!
Charles

From: MimiS on 21 May 2010 17:44
You could set up a "summary sheet" but it would involve some maintenance.

Start a summary sheet with a list of all customers--the minimum you want to
report by--along with their relevant codes.
Add 2 columns to the left of your imported data: index and month.
Index = month&(customer code cell) so you end up with a dummy index that is
the date + the customer code, assuming each is unique. The result might look
something like 43298123456789.

you can then do a vlookup on the summary sheet using the dummy index created
in the details sheet and sum amounts by month & customer. monthly columns on
summary must be in same format that you used on the detail sheet.

The maintenance is you'll need to add customers and codes as new ones crop
up, and you have to type the date and copy the index formula down to new data
as it's entered. I have this setup for a couple of my workbooks and it works
fairly well.

the formula below combines a quote# with a product line and finds the
relevant percentage on the vlookup pcent table and multiplies the total quote
amount by the percentage found. it returns -0- if the quote+product line
isn't found. row 13 has the product line, column A has the quote#, and
column d has total quote cost.

=ROUND(IF(ISERROR((VLOOKUP(CONCATENATE("'",$A40,M$13),'vlookup
pcent'!$B$2:$K$10000,10,FALSE)*$D40)),0,(VLOOKUP(CONCATENATE("'",$A40,M$13),'vlookup pcent'!$B$2:$K$10000,10,FALSE)*$D40)),2)

sorry for the long answer, hope this helps.


"Charles" wrote:

> I have a sales order spreadsheet that gets updated daily from our ERP system.
> The worksheet has the following columns:
> 1. Customer code (each customer could have several entries per month)
> 2. Order date (the current worksheet has two years of history in addition to
> the daily updates)
> 3. Amount
>
> What I would like to do is make a new worksheet that has the following
> columns:
> 1. Customer code listed once for each customer
> 2. Total sales summed by month ( so I would have a column for each 2 years
> of history and will add each new month)
>
> I know I could do this with a pivot table but for internal company reasons I
> would like to know if anyone can tell me how to do it with a formula.
>
> Thanks in advanced for your help!!!
> Charles
>
 | 
Pages: 1
Prev: How do I remove the PAGE 1 from an excell worksheet?
Next: Custom formating