inefficient sheets

Hi,

Any help/advice would be greatly appreciated. I am fairly new to Excel, and only have experience of basic formatting and formulas.

I have two existing spreadsheets that seem to me to be incredibly inefficient and hard to update. I would like to overhaul them!

I have attached a sample of each. It seems to me that all the data can/should exist in one spreadsheet. I currently have to manually transfer data from one to the other in order to keep them updated.

Spreadsheet 1 currently contains the following information:

Customer name, customer type, weekly sales, ytd sales.

Spreadsheet 2 has been designed to compare and monitor the sales of customers within groups. (i.e a restaurant chain).

--------

If I add a column in spreadsheet 1 with the name of the group then surely I can create a filter that displays just the groups sales on another page? I still need to be able to compare this years sales to previous years (as seen in spreadsheet 2)

--------

I also need to be able to easily add new customers.

--------

So:

I need to setup/design a new spreadsheet with the following information:

Customer name, customer type, customer group code, weekly sales for entire year.

The data will be added on a weekly basis, which will include the addition of new customers. Obviously there will be many columns (52 for each weeks sales, plus extras for monthly and yearly totals, plus customer info).

From the data I will want to extract and display the following information in another sheet/s:

Sales data by customer type, sales data by group (i.e restaurants owned by same company), top 10 customers, top 10 customers by type. Plus an easy to read comparison to previous years sales.

Any advice greatly appreciated!

I hope I have explained it well enough...

Hi
there are no attachments.
Care to try again ?

oops, here are the attachments

Can anyone help? Cheers...

Also,

I need to be able to compare new customers (say within the past year) sales as a percentage of total sales. New customers can be part of groups as well.

I'm stuck...!

If the PT solution is what you look for you can also edit sales as percentage of total sales.
More info

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

Hi,

I just cannot get the hang of these pivot tables - if I use your example I would have to have thousands of entries in the rows because you have moved the dates from a row to a column? Its going to be a very slow process moving all the data?!

thanks...