VLOOKUPS and Template

[ozcank]

Hi All

Ok, I have a tricky rpoblem. What I have is a worksheet full of sales
information, for example, col a has the directors name, col b has the
managers name, col c has the store name, col d has the sales value for
store, col e for manager and col f for director. There are 5 diferent
directors who look after 20 different managers, who in turn look after
50 stores. So col a has repeating director name until the data for next
director starts (about 20 rows), col b has the manager's name repeating
(about 15 times) and col c has a row for each store, this tells me
which store is in which manager group and which director group.

What I would like to be able to do is set up a worksheet for each
manager and have the manager's total on top, his stores and stores
sales beneath and the director for the area total at the bottom.

The main sheet with all the data is a weekly download where the stores
may change the manager area they're in or even the director area. I
know this sounds confusing but I'd like to be able to produce this
report each week where the store names in each manager's worksheet
update according to whatever store he has that week and his director's
sales info displayed at the bottom.

If anyone has the vaguest idea what I'm talking about, that's a
brilliant start, if you can help me with this, I would be forever
greatful.

Many thanks

Ozkan

 

[Guest]

Have you played with pivot tables? You will be a little limited in terms of
how the data is displayed, but they will total up the numbers for you, and
can be set up to autoupdate fresh data in a range (or from a database). If
you can live with some restrictions on the appearance of how the output data
is displayed, this might be the cleanest solution.
-Ed

 

[ozcank]

Yeah I've had a go with pivot tables, the problem is that I then have to
write a macro to break up the pivot table and get the information for
each section manager, and then paste it into a new worksheet of its own
and also add the director total at the bottom of the section of each
worksheet, and this is really tricky. I've had some useful reply from
Domenic which seems to be working out really well.

Thanks for your reply.

Oz