View Single Post
  #7   Report Post  
Conrad Carlberg
 
Posts: n/a
Default

Oops. In paragraph 5 of my lengthy reply (I do tend to go on) I wrote this:

Insert | Range | Define

but I meant this:

Insert | Name | Define.

Sorry. In my own defense: a little later on when I start to describe the
actual steps you'd take, I did get it right.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"baz" wrote in message
...
Carl

Source looks like this. The tab carrys the weekly date. This is week
6\9\05 so it will fill in the RollUp under that date. The next weeks
numbers, a separate data sheet in the same format, will fill in the
next week, etc.,etc.,etc. as I get the data.

Area Store UPC Sold
IDAHO 101 66097322361 2
IDAHO 101 66097322367 2
IDAHO 101 66097322371 4
IDAHO 101 66097322372 2
IDAHO 101 66097322377 1
IDAHO 102 66097322361 2
IDAHO 103 66097322361 9
IDAHO 103 66097322367 21
IDAHO 103 66097322371 19
IDAHO 103 66097322372 9
IDAHO 103 66097322377 8
IDAHO 119 66097322361 3
IDAHO 119 66097322367 3
IDAHO 119 66097322371 5
IDAHO 119 66097322372 6
IDAHO 119 66097322377 1
IDAHO 126 66097322361 1
IDAHO 126 66097322367 2
IDAHO 126 66097322371 2
IDAHO 126 66097322372 1
IDAHO 126 66097322377 1
IDAHO 130 66097322361 3
IDAHO 130 66097322363 22
IDAHO 131 66097322361 1
IDAHO 131 66097322367 1
IDAHO 131 66097322371 2
IDAHO 131 66097322377 2
IDAHO 132 66097322377 1
IDAHO 138 66097322361 12
IDAHO 138 66097322367 8

RollUp Looks like this below.

I want to be able to have values for say Store 101 fill into the
proper cells in the Roll Up for store 101. Store 101 does not carry
all items but this will be used for other areas that may carry
different items so I'm thinking universality (relative range).


I hope this is clearer.

Thanks,

Brad


Store TL 2 Date Avg/Wk 6/9/2005 6/16/2005
6/23/2005
101 0 0 0
0
22360 Org String

22361 StrawString 0

22362 BlueBString

22363 CrnAplString 0

22367 AsstString 0

22370 OrgNugget

22371 StrawNugget 0

22372 BlueBNugget 0

22373 CrnAplNugget

22377 AsstNugget 0

102 0 0 0 0
0
22360 Org String

22361 StrawString 0

22362 BlueBString

22363 CrnAplString 0

22367 AsstString 0

22370 OrgNugget

22371 StrawNugget 0

22372 BlueBNugget 0

22373 CrnAplNugget

22377 AsstNugget 0

103 0 0 0 0
0
22360 Org String

22361 StrawString 0

22362 BlueBString

22363 CrnAplString 0

22367 AsstString 0

22370 OrgNugget

22371 StrawNugget 0

22372 BlueBNugget 0

22373 CrnAplNugget

22377 AsstNugget 0

119 0 0 0 0
0
22360 Org String

22361 StrawString 0

22362 BlueBString

22363 CrnAplString 0

22367 AsstString 0

22370 OrgNugget

22371 StrawNugget 0

22372 BlueBNugget 0

22373 CrnAplNugget

22377 AsstNugget 0




On Sat, 03 Sep 2005 00:28:42 GMT, "Conrad Carlberg"
wrote:

Hi baz,

As I read the description of your situation, it sounds to me as though

it's
almost perfect for a pivot table that's based on a dynamic range. I say
"almost" because you'd want to reconfigure the sales information as a

true
Excel list, with Store name in one column, Item name in another column,
Sales data in one or more other columns (number of items and revenue),

which
week in another column. E.g.:

Store Item Count Revenue Date
A Pez 8 $100 6/09/05
A Trix 3 $20 6/09/05
B Pez 7 $92 6/09/05

etc.

You don't describe your weekly sales report in detail, but a dynamic

range
could be defined, using Insert | Name | Define, as something like this

for
the RefersTo:

=OFFSET($A$1,1,0,COUNTA($A:$A),COUNTA($1:$1))

This dynamically-defined range will redefine its address according to the
number of alphanumeric values in column $A -- giving the number of rows

in
the range -- and the number of alphanumeric values in row $1 -- giving

the
number of columns in the range. When you get a new date in a column, the
range will get wider. When and if you get a new item or store, the range
will get taller.

Now, base a pivot table on that named range. You could summarize sales by
number of items and/or dollar amounts, for each item, for each store, for
each week, simultaneously and without having to use worksheet functions.
Another nice aspect is that you can call for a pivot chart that will
summarize the data visually, without having to construct the chart

manually.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005

"baz" wrote in message
...
Please post response as email is bogus to prevent spam.

I have posted this query to the misc board also. I just need some
direction.

I am using Excel 2003. I hope I am clear in my description of my need.

I get a weekly sales report and want to roll the numbers up into a
ROLL UP sheet. The roll up will be static but the weekly sheet is
dynamic (can fluctuate in the reporting). Universal to both is a store
number, a UPC (identifier), and amount sold.

The weekly sales will appear across columns (for tracking purposes).
Example: 06-09-05 data will appear in column D, 06-15-05 data will
appear in column E, etc.

The other info will be like shown down column A:

(Store)101
Item 1
Item 2
Item 3
(Store)102
Item 1
Item 2
Item 3


In the roll up I want to put a function that will search the weekly
data (I know I will have to change source page name in the function
weekly) that will give me any sales for store 101 of the three items.
Each week I will build a new function for the column I want the data
to appear in.

Now, items may have sales, so the store number would show up with item
and amount, or if there are no sales store number may not appear on
weekly report.

I have tried to think this out but not sure how to proceed. Is this a
Match function, an Index function, or a combo of both?

I appreciate any help or solution.

Thanks in advance,

baz