Excel layout help... 5

[jfussell]

This is a BIG and time consuming question, so any suggestions would be greatly appreciated.
I've tried 2 different ways with my layout and calculations but neither way works for me, so I'm looking for suggestions on the following:

Worksheet 1: purpose is to enter the monthy sales per customer per item for the year. So, as an example I have Medical Center as a customer, for each customer we sell the same 3 services: installatiion, training and support. Here is how I currently have it laid out...

Column A | Column B | Column C | Column D
Medical Center | Installation | Jan. sales | Feb. sales, etc.
Medical Center | Training | Jan. sales | Feb. sales, etc.

Here's my dilemma: I would like a recap on a separate worksheet to recap the total sales by customer by month, then another worksheet to recap the total sales by services by month. I've tried using manual calculation, but then it becomes cumbersome everytime I add a new customer. I've also tried Pivot tables but there are lots of problems using those as well. Isn't there a simpler way by maybe using LOOKUP formulas to capture these 2 "reports" separately?

Again, many thanks to those who would be able to help me.

J

 

[jgurgen]

it would be a lot easier if you created a database probably in Access and stored everything in that. This way anytime you add a new customer you still can do the same things.

 

[xlhelp]

Please look at DSUM function under Excel help.

You will need to update your criteria table as you add clients

 

[SkipVought]

How is your source data table set up?

This could be done in about 15 seconds with the Pivot Table Wizard, if your data is formatted properly.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[jfussell]

Thank you all for your suggestions.

Access database was going to be my last option due to network and data import to Excel complications. There are multiple people that use this spreadsheet.

I haven't tried DSUM formula before so will have to play with that a bit before I'd know if that is the solution.

Skip, I have used Pivot Table and that is currently what's staying there until I find a better solution. Too many problems using Pivot Tables simply because there are other users that use this spreadsheet and it causes havoc because others don't know how it works. Hence my option to use hopefully some formulas and lock down those cells.

Give me a day or so and I'm going to give the DSUM option a try.

Thanks again.

 

[sahmiele]

This should get you by. it will clear the 2 "report sheets" of old report data, and re-create the reports. Just copy and paste it into a macro you create. Substitute "Sheet1" with you data sheet name, "Sheet2" with you totals by customer sheet name, and "sheet3" with your totals by service sheet name.


Sheets(Array("Sheet2", "Sheet3")).Select
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("Sheet1").Activate
Cells.Select
Selection.Copy
Sheets(Array("Sheet2", "Sheet3")).Select
Sheets("Sheet2").Activate
Range("A1").Paste
Sheets("Sheet2").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, _
7, 8, 9, 10, 11, 12, 13, 14), Replace:=True, PageBreaks:=False, SummaryBelowData _
:=True
Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("Sheet3").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, _
7, 8, 9, 10, 11, 12, 13, 14), Replace:=True, PageBreaks:=False, SummaryBelowData _
:=True
Cells.Select
Cells.EntireColumn.AutoFit




Hope this helps!

 

[firefytr]

sahmiele, did you just copy/paste from the macro recorder???

Check out Debra Dalgleish's website on dynamic pivot table names/ranges ...

http://www.contextures.com/xlPivot01.html

-----------
Regards,
Zack Barresse