A way to display key info from a large database?

[Nathalie]

Hello everyone!

I am currently working for a sales division at large Company as a sales rep.
I would also like to add that I am not a CS person but a business person, so
please be patient!
What I need is this:
We're required to fill in a "one sheet" that displays sales data by
category, market share, etc (some other data) of a single store. Its a single
sheet, the problem is that I need to fill in 45 of them (45 stores), every
month (updating them).
In order to fill this I need to get shipments data, scanner sales data etc.
From different excel databases. There's a single reference value (store
number) that I could use to sort and arrange the data somehow. I was
wondering if I could do this using a macro or how?

I've seen excel files doing what I want to do, but I don't see they have a
macro, they might be using something else.

I'd appreciate greatly any help.

Thanks everyone!
Nathalie-Mexico City

 

[smartin]

Hello everyone!

I am currently working for a sales division at large Company as a sales rep.
I would also like to add that I am not a CS person but a business person, so
please be patient!
What I need is this:
We're required to fill in a "one sheet" that displays sales data by
category, market share, etc (some other data) of a single store. Its a single
sheet, the problem is that I need to fill in 45 of them (45 stores), every
month (updating them).
In order to fill this I need to get shipments data, scanner sales data etc.
From different excel databases. There's a single reference value (store
number) that I could use to sort and arrange the data somehow. I was
wondering if I could do this using a macro or how?

I've seen excel files doing what I want to do, but I don't see they have a
macro, they might be using something else.

I'd appreciate greatly any help.

Thanks everyone!
Nathalie-Mexico City

Here is a simple example that might help you. The idea is to create a
worksheet that works for one Store, using the Store number as a
"variable" that can be quickly changed.

Suppose one of your source tables looks like this, in worksheet
StoreCatSales:

(col A B C )
Store Category Sales
1 X 1000
1 Y 2000
1 Z 3000
2 X 800
2 Y 200

In your summary sheet for Store 1 you want Sales by Category for that
single Store.

A1 = "Store:"
B1 = 1 (this is the "variable" Store identifier)

A2 = "Category"
B2 = "Sales"

A3 = "X"
A4 = "Y"
A5 = "Z"

B3
=SUMPRODUCT(($B$1=StoreCatSales!$A$2:$A$9999)*(A3=StoreCatSales!$B$2:$B$9999)*StoreCatSales!$C$2:$C$9999)

fill this down through B5. There is your Sales by Category for Store 1.

Copy this entire worksheet to a new worksheet. In the copy, change cell
B1 to 2. There is your Sales by Category for Store 2. Make more copies
of the worksheet for additional Stores.

Hope this helps!