select specific cells and consolidate same over many worksheets

[MikeR-Oz]

Given that my excel skills are limited at present, I have a task for work
that I hope you can help with.

A series of saved excel files that are based on weeks - Monday to Sunday.
The sheets are arranged with 1st column being a store with various stores
runing down in rows(not each week shows the same sytores however there are
many times where the same store is shown in seperate sorkbooks). The days of
the week are also aranged in columns- Mon, Tue Wed, Thur, etc.

The cells below each day for the store shows total sales .

So the workbook for the week will show the total sales per store per day.

Now I have multiple weeks and want to see what TRENDS store (A) does on
Mondays ONLY and then Tuesday ONLY etc over the many weeks in order to see if
one day is better than another for that store and then repeat for each store
(A) to (L)

Then do for each store and then clusters of stores which make up regions.

Is this possible easily?

Please advise your options and approach for me . I have thought of and tried
consolidate feature but that just added Monday store (A) of week 1 to same
for week 2 etc .

Thanks

Michael

 

[Roger Govier]

Hi Mike

One way would be to copy data from each of your separate files to one
sheet in an excel workbook.
I assume each sheet currently has 8 columns, one for store name and 7
for the days of the week and since you mention stores A-L then it sounds
as though there would only be about 12 lines of data on each source
sheet.
On the new Sheet being created, add a 9th column called Week Number.
Now, as you copy each successive block of data, fill in column 9 with
the week number that it relates to.

If you have a huge number of files to copy from, then Ron de Bruin has
some nice macro solutions to automate this task
http://www.rondebruin.nl/copy3.htm
I think you would probably need
http://www.rondebruin.nl/copy3.htm#header

Now with you new consolidated data, set up a Pivot Table, which will
allow you to carry out any of the analyses you require.
For more information on setting up Pivot Tables, take a look at Debra
Dalgleish's site
http://www.contextures.com/tiptech.html
and scroll to the section on Pivot Tables

I hope this guides you in the right direction.

 

[MikeR-Oz]

Thanks Roger for your efforts - I will work through what you have said and
referred me to, but at first glance it looks a bit beyond my knowledge of
excel - but will try -

Thanks for the pont in the right direction.
Mike