Consolidate / pivot challenge

[Snoopy]

Hey guys
I have this 3 level reporting system - where ground-level is
registrating data like this:
(1 file pr salesman - there are several salesmen and departments)

Dep Salesman Year week customer value
A Jack 2008 01 x 10
A Jack 2008 01 y 5
A Jack 2008 02 z 15
A Jack 2008 02 aa 8
A Jack 2008 02 z 11
.....
2009 -> likewise

My macro collects all data details from all salesmanfiles (into a new
file) and put them together for further "level 2"-reporting by using
pivot tables. This works fine
The manager of level 2 will though have access to the very detail
behind the summaries.
On this Dep-level the data volum i s no problem.

Then the challenge:
The top level - level 3 - will only deal with summariesep/Year/week
- and NOT get access to the details (as salesman or customer) behind.
If alle salesmens registrations should be the base of this pivottable
it would be to big for the excel in some time - so I have to compress
the volume of datas.
Then: How can I collect the Dep/Year/Week summaries from each Dep-
file / are there any smart Excel-function that consolidates my dep-
table like this:

Dep Year Week Value

Its hard to explain the problem more precise, but I hope some of you
understands and will give me some advise

Regards
Snoopy

 

[ShaneDevenshire]

Hi,

You haven't said how you want the consolidate data to display, so just
combining it you could use the Data, Consolidate command, which works for
closed workbooks. Make it easy on yourself by assigning range names to the
range you want to consolidate. You can put entire process in a macro and
then you can give the user a button.

 

[Snoopy]

Hi,

You haven't said how you want theconsolidatedata to display, so just
combining it you could use the Data,Consolidatecommand, which works for
closed workbooks.  Make it easy on yourself by assigning range names tothe
range you want toconsolidate.  You can put entire process in a macro and
then you can give the user a button.  

--
Thanks,
Shane Devenshire











– Vis sitert tekst –

Thanks again Devon for your consideration - I'll think something out i
the way you advice me to
Regards
Snoopy