Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 2nd 05, 09:09 PM
baz
 
Posts: n/a
Default Help PLEASE! Not sure what answer is: Match? Index? Other?

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

  #2   Report Post  
Old September 3rd 05, 01:28 AM
Conrad Carlberg
 
Posts: n/a
Default

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



  #3   Report Post  
Old September 3rd 05, 02:32 AM
baz
 
Posts: n/a
Default

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




  #4   Report Post  
Old September 3rd 05, 07:02 AM
Conrad Carlberg
 
Posts: n/a
Default

Hi Brad,

Okay, that's a good clarification. This is probably going to seem like
entirely too much work, but it's going to save you a lot of time in the long
run.

First, you need what Excel calls a list. A list in Excel has field/variable
names in its first row, and the values of those fields in its second and
subsequent rows. The list is just a matter of laying things out properly.

With the data as you describe it, your list would have five columns, and
each column would be headed by the name of a different variable. So, cell A1
would contain the name Area, B1 the name Store, C1 would contain the name
UPC, D1 the name Sold, E1 the name Date.

The way you're getting your data conforms to that structure, except for
Date, which is just on the sheet tab. You could type that date in E2, under
the name Date, and copy-and-paste that value into as many rows as you have
records for the other variables. Or if this is something that will recur
each week for many weeks, you might consider recording and saving a VBA
macro that picks up the name of the worksheet tab and copies it into column
E for all the new records you've received.

I also suspect that you want to continue your weekly data for many weeks and
do your analysis over time, rather than in one shot analyses that each look
at one week only. In that case, you're going to want what's called a dynamic
data range. Worksheet ranges, like A1:E500, can be named in various ways,
such as Insert | Range | Define. If you handle things the right way, you can
get Excel to automatically redefine a range's address according to how many
rows and columns are in it.

As you describe things, you would have five columns only, so all your
dynamic data range would have to figure out is how many rows it has. To keep
things straightforward, assume that the Area values in column A define the
number of rows in the data range: if there are 200 valid Area values, there
are 200 valid sales records. (There are ways to avoid making this
assumption.)

So, on the worksheet where you have put all your data, both the current and
the prior, you could define (you need do so once only) a name such as
SalesData, by choosing Insert | Name | Define, typing SalesData into the
Names in Workbook box, and this formula into the Refers to box:

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

This range definition starts with cell A1 (argument 1) and offsets the range
by 0 rows (argument 2) and zero columns (argument 3). It offsets the range
by zero rows because you need to tell Excel the variable/field names in Row
1. Because you have one data record for each value in Column A, less 1 for
the variable name in Row 1, Excel makes the range have that many rows, but
it includes the first row for the variable names, because you have to pass
those along to your pivot table (see below). And as your data comes to you,
and after you've pasted the date into column E, the range has five columns.

Each time you get another set of data, you can copy that data and paste it
into this data range, meanwhile copying the date into the associated cells
in Column E. The range named SalesData will redefine itself when Excel
counts the newly pasted values it finds in Column A.

(BTW, one drawback with this sort of dynamic name is that the name does not
appear in the Name Box. I'm in hopes that this longtime oversight will be
corrected in Excel 12.)

The hard work is now done, and you're about to start saving time, big time.
Somewhere else -- presumably in the same workbook and, to keep things neat,
on a different worksheet, you establish a pivot table. The steps differ a
bit depending on which Excel version you're using, and I'll assume a more
recent one. Choose Data | PivotTable and PivotChart Report. In the pivot
table wizard's first step, choose Microsoft Excel List or Database, and
accept the default PivotTable report (you can change it to a chart/report
later, and this is simpler).

In step 2, type the name of the sales data range -- I suggested SalesData
above, but it can be anything that isn't smutty. Actually, it could be
smutty, but I promised my mother years ago that I'd never base a pivot table
on a data range that had a smutty name.

In step 3, indicate where you want to put the pivot table.

Then, the pivot table wizard disappears and you drag fields into the table
layout on the worksheet. You might want to drag Date into the Row area, UPC
into the Column area, and Sold into the Data area. Assuming that you have
exclusively numeric values in the Sold field, it will default to Sum, and as
I've described the table's layout you'll get the sum of units sold for each
date and each UPC.

You can have more row and column fields, though. One arrangement might have
Sold in the Data area, Date in the Column area, and both Area and Store in
the Row area. This would give you a subtotal for store within area.

BTW, you can group on the Date field to force it to show sales by something
other than week -- month, quarter, year, quarter within year, etc. This sort
of thing is why I've always called pivot tables the most powerful method of
data analysis and synthesis in Excel.

There's another kind of field in pivot tables, a Page Field. It's a way of
selecting a subset of records. If you put, say, Area into a Page field, you
can look at your table for a particular Area that you select via a dropdown.

Also, bear in mind that a pivot table does not automatically react to the
presence of new data. You'll want to right-click a cell in the table and
choose Refresh Data from the shortcut menu. Although your dynamic data range
automatically refreshes itself when new data comes along, the pivot table
doesn't, and you need to call its attention to the fact that more data has
come along. (Again, there are ways to automate that, but at the outset it's
best to keep things straightforward.)

--
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






  #5   Report Post  
Old September 3rd 05, 11:05 AM
PY & Associates
 
Posts: n/a
Default

Been watching this message for a while. Very heavy for my reading. Wish I
can assist, but there are requirements that I need clarification. As the
message is getting too long, would you consider contacting me direct please?

Data file
Date appears as sheet name
always 4 columns thus
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

Roll up report
I cannot understand what these are
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


Is the following part of roll up report?
=====
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.

=====
If so, why the dates are not 7 days apart?
Also
I trust columns A to C in roll up report have no relationship with data.

Regards
PY & Associates

"Conrad Carlberg" wrote in message
nk.net...
Hi Brad,

Okay, that's a good clarification. This is probably going to seem like
entirely too much work, but it's going to save you a lot of time in the

long
run.

First, you need what Excel calls a list. A list in Excel has

field/variable
names in its first row, and the values of those fields in its second and
subsequent rows. The list is just a matter of laying things out properly.

With the data as you describe it, your list would have five columns, and
each column would be headed by the name of a different variable. So, cell

A1
would contain the name Area, B1 the name Store, C1 would contain the name
UPC, D1 the name Sold, E1 the name Date.

The way you're getting your data conforms to that structure, except for
Date, which is just on the sheet tab. You could type that date in E2,

under
the name Date, and copy-and-paste that value into as many rows as you have
records for the other variables. Or if this is something that will recur
each week for many weeks, you might consider recording and saving a VBA
macro that picks up the name of the worksheet tab and copies it into

column
E for all the new records you've received.

I also suspect that you want to continue your weekly data for many weeks

and
do your analysis over time, rather than in one shot analyses that each

look
at one week only. In that case, you're going to want what's called a

dynamic
data range. Worksheet ranges, like A1:E500, can be named in various ways,
such as Insert | Range | Define. If you handle things the right way, you

can
get Excel to automatically redefine a range's address according to how

many
rows and columns are in it.

As you describe things, you would have five columns only, so all your
dynamic data range would have to figure out is how many rows it has. To

keep
things straightforward, assume that the Area values in column A define the
number of rows in the data range: if there are 200 valid Area values,

there
are 200 valid sales records. (There are ways to avoid making this
assumption.)

So, on the worksheet where you have put all your data, both the current

and
the prior, you could define (you need do so once only) a name such as
SalesData, by choosing Insert | Name | Define, typing SalesData into the
Names in Workbook box, and this formula into the Refers to box:

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

This range definition starts with cell A1 (argument 1) and offsets the

range
by 0 rows (argument 2) and zero columns (argument 3). It offsets the range
by zero rows because you need to tell Excel the variable/field names in

Row
1. Because you have one data record for each value in Column A, less 1 for
the variable name in Row 1, Excel makes the range have that many rows, but
it includes the first row for the variable names, because you have to pass
those along to your pivot table (see below). And as your data comes to

you,
and after you've pasted the date into column E, the range has five

columns.

Each time you get another set of data, you can copy that data and paste it
into this data range, meanwhile copying the date into the associated cells
in Column E. The range named SalesData will redefine itself when Excel
counts the newly pasted values it finds in Column A.

(BTW, one drawback with this sort of dynamic name is that the name does

not
appear in the Name Box. I'm in hopes that this longtime oversight will be
corrected in Excel 12.)

The hard work is now done, and you're about to start saving time, big

time.
Somewhere else -- presumably in the same workbook and, to keep things

neat,
on a different worksheet, you establish a pivot table. The steps differ a
bit depending on which Excel version you're using, and I'll assume a more
recent one. Choose Data | PivotTable and PivotChart Report. In the pivot
table wizard's first step, choose Microsoft Excel List or Database, and
accept the default PivotTable report (you can change it to a chart/report
later, and this is simpler).

In step 2, type the name of the sales data range -- I suggested SalesData
above, but it can be anything that isn't smutty. Actually, it could be
smutty, but I promised my mother years ago that I'd never base a pivot

table
on a data range that had a smutty name.

In step 3, indicate where you want to put the pivot table.

Then, the pivot table wizard disappears and you drag fields into the table
layout on the worksheet. You might want to drag Date into the Row area,

UPC
into the Column area, and Sold into the Data area. Assuming that you have
exclusively numeric values in the Sold field, it will default to Sum, and

as
I've described the table's layout you'll get the sum of units sold for

each
date and each UPC.

You can have more row and column fields, though. One arrangement might

have
Sold in the Data area, Date in the Column area, and both Area and Store in
the Row area. This would give you a subtotal for store within area.

BTW, you can group on the Date field to force it to show sales by

something
other than week -- month, quarter, year, quarter within year, etc. This

sort
of thing is why I've always called pivot tables the most powerful method

of
data analysis and synthesis in Excel.

There's another kind of field in pivot tables, a Page Field. It's a way of
selecting a subset of records. If you put, say, Area into a Page field,

you
can look at your table for a particular Area that you select via a

dropdown.

Also, bear in mind that a pivot table does not automatically react to the
presence of new data. You'll want to right-click a cell in the table and
choose Refresh Data from the shortcut menu. Although your dynamic data

range
automatically refreshes itself when new data comes along, the pivot table
doesn't, and you need to call its attention to the fact that more data has
come along. (Again, there are ways to automate that, but at the outset

it's
best to keep things straightforward.)

--
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









  #6   Report Post  
Old September 3rd 05, 01:02 PM
PY & Associates
 
Posts: n/a
Default

Let me guess the requirements as I understand:

Using data file with 4 columns(Area/Store/UPC/Sold)
we sort by area, then by store, then by UPC
get subtotal by UPC
rearrange the data in next available column in roll up sheet
first cell=date=data sheet name
2nd cell= area
3rd cell=store
4th cell=UPC1 units sold
5th cell=UPC2 units sold
..
..
..
repeat 3rd, 4th, 5th....cells

If affirmative, not so difficult then.
--
Regards
PY & Associates

"PY & Associates" wrote in message
...
Been watching this message for a while. Very heavy for my reading. Wish I
can assist, but there are requirements that I need clarification. As the
message is getting too long, would you consider contacting me direct

please?

Data file
Date appears as sheet name
always 4 columns thus
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

Roll up report
I cannot understand what these are
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


Is the following part of roll up report?
=====
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.

=====
If so, why the dates are not 7 days apart?
Also
I trust columns A to C in roll up report have no relationship with data.

Regards
PY & Associates

"Conrad Carlberg" wrote in message
nk.net...
Hi Brad,

Okay, that's a good clarification. This is probably going to seem like
entirely too much work, but it's going to save you a lot of time in the

long
run.

First, you need what Excel calls a list. A list in Excel has

field/variable
names in its first row, and the values of those fields in its second and
subsequent rows. The list is just a matter of laying things out

properly.

With the data as you describe it, your list would have five columns, and
each column would be headed by the name of a different variable. So,

cell
A1
would contain the name Area, B1 the name Store, C1 would contain the

name
UPC, D1 the name Sold, E1 the name Date.

The way you're getting your data conforms to that structure, except for
Date, which is just on the sheet tab. You could type that date in E2,

under
the name Date, and copy-and-paste that value into as many rows as you

have
records for the other variables. Or if this is something that will recur
each week for many weeks, you might consider recording and saving a VBA
macro that picks up the name of the worksheet tab and copies it into

column
E for all the new records you've received.

I also suspect that you want to continue your weekly data for many weeks

and
do your analysis over time, rather than in one shot analyses that each

look
at one week only. In that case, you're going to want what's called a

dynamic
data range. Worksheet ranges, like A1:E500, can be named in various

ways,
such as Insert | Range | Define. If you handle things the right way, you

can
get Excel to automatically redefine a range's address according to how

many
rows and columns are in it.

As you describe things, you would have five columns only, so all your
dynamic data range would have to figure out is how many rows it has. To

keep
things straightforward, assume that the Area values in column A define

the
number of rows in the data range: if there are 200 valid Area values,

there
are 200 valid sales records. (There are ways to avoid making this
assumption.)

So, on the worksheet where you have put all your data, both the current

and
the prior, you could define (you need do so once only) a name such as
SalesData, by choosing Insert | Name | Define, typing SalesData into the
Names in Workbook box, and this formula into the Refers to box:

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

This range definition starts with cell A1 (argument 1) and offsets the

range
by 0 rows (argument 2) and zero columns (argument 3). It offsets the

range
by zero rows because you need to tell Excel the variable/field names in

Row
1. Because you have one data record for each value in Column A, less 1

for
the variable name in Row 1, Excel makes the range have that many rows,

but
it includes the first row for the variable names, because you have to

pass
those along to your pivot table (see below). And as your data comes to

you,
and after you've pasted the date into column E, the range has five

columns.

Each time you get another set of data, you can copy that data and paste

it
into this data range, meanwhile copying the date into the associated

cells
in Column E. The range named SalesData will redefine itself when Excel
counts the newly pasted values it finds in Column A.

(BTW, one drawback with this sort of dynamic name is that the name does

not
appear in the Name Box. I'm in hopes that this longtime oversight will

be
corrected in Excel 12.)

The hard work is now done, and you're about to start saving time, big

time.
Somewhere else -- presumably in the same workbook and, to keep things

neat,
on a different worksheet, you establish a pivot table. The steps differ

a
bit depending on which Excel version you're using, and I'll assume a

more
recent one. Choose Data | PivotTable and PivotChart Report. In the pivot
table wizard's first step, choose Microsoft Excel List or Database, and
accept the default PivotTable report (you can change it to a

chart/report
later, and this is simpler).

In step 2, type the name of the sales data range -- I suggested

SalesData
above, but it can be anything that isn't smutty. Actually, it could be
smutty, but I promised my mother years ago that I'd never base a pivot

table
on a data range that had a smutty name.

In step 3, indicate where you want to put the pivot table.

Then, the pivot table wizard disappears and you drag fields into the

table
layout on the worksheet. You might want to drag Date into the Row area,

UPC
into the Column area, and Sold into the Data area. Assuming that you

have
exclusively numeric values in the Sold field, it will default to Sum,

and
as
I've described the table's layout you'll get the sum of units sold for

each
date and each UPC.

You can have more row and column fields, though. One arrangement might

have
Sold in the Data area, Date in the Column area, and both Area and Store

in
the Row area. This would give you a subtotal for store within area.

BTW, you can group on the Date field to force it to show sales by

something
other than week -- month, quarter, year, quarter within year, etc. This

sort
of thing is why I've always called pivot tables the most powerful method

of
data analysis and synthesis in Excel.

There's another kind of field in pivot tables, a Page Field. It's a way

of
selecting a subset of records. If you put, say, Area into a Page field,

you
can look at your table for a particular Area that you select via a

dropdown.

Also, bear in mind that a pivot table does not automatically react to

the
presence of new data. You'll want to right-click a cell in the table and
choose Refresh Data from the shortcut menu. Although your dynamic data

range
automatically refreshes itself when new data comes along, the pivot

table
doesn't, and you need to call its attention to the fact that more data

has
come along. (Again, there are ways to automate that, but at the outset

it's
best to keep things straightforward.)

--
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









  #7   Report Post  
Old September 3rd 05, 03:39 PM
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






  #8   Report Post  
Old September 3rd 05, 03:47 PM
Conrad Carlberg
 
Posts: n/a
Default

Hi PY --

Well, no. The section of baz's message that you quote, the paragraph that
begins "The weekly sales ...", is not part of the report, but is rather part
of his description of the situation.

By putting the raw data into an Excel list structure, and basing a pivot
table on that list, there's no need to sort and subtotal. The pivot table
takes care of those housekeeping chores, thanks be.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"PY & Associates" wrote in message
...
Let me guess the requirements as I understand:

Using data file with 4 columns(Area/Store/UPC/Sold)
we sort by area, then by store, then by UPC
get subtotal by UPC
rearrange the data in next available column in roll up sheet
first cell=date=data sheet name
2nd cell= area
3rd cell=store
4th cell=UPC1 units sold
5th cell=UPC2 units sold
.
.
.
repeat 3rd, 4th, 5th....cells

If affirmative, not so difficult then.
--
Regards
PY & Associates

"PY & Associates" wrote in message
...
Been watching this message for a while. Very heavy for my reading. Wish

I
can assist, but there are requirements that I need clarification. As the
message is getting too long, would you consider contacting me direct

please?

Data file
Date appears as sheet name
always 4 columns thus
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

Roll up report
I cannot understand what these are
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


Is the following part of roll up report?
=====
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.

=====
If so, why the dates are not 7 days apart?
Also
I trust columns A to C in roll up report have no relationship with data.

Regards
PY & Associates

"Conrad Carlberg" wrote in message
nk.net...
Hi Brad,

Okay, that's a good clarification. This is probably going to seem like
entirely too much work, but it's going to save you a lot of time in

the
long
run.

First, you need what Excel calls a list. A list in Excel has

field/variable
names in its first row, and the values of those fields in its second

and
subsequent rows. The list is just a matter of laying things out

properly.

With the data as you describe it, your list would have five columns,

and
each column would be headed by the name of a different variable. So,

cell
A1
would contain the name Area, B1 the name Store, C1 would contain the

name
UPC, D1 the name Sold, E1 the name Date.

The way you're getting your data conforms to that structure, except

for
Date, which is just on the sheet tab. You could type that date in E2,

under
the name Date, and copy-and-paste that value into as many rows as you

have
records for the other variables. Or if this is something that will

recur
each week for many weeks, you might consider recording and saving a

VBA
macro that picks up the name of the worksheet tab and copies it into

column
E for all the new records you've received.

I also suspect that you want to continue your weekly data for many

weeks
and
do your analysis over time, rather than in one shot analyses that each

look
at one week only. In that case, you're going to want what's called a

dynamic
data range. Worksheet ranges, like A1:E500, can be named in various

ways,
such as Insert | Range | Define. If you handle things the right way,

you
can
get Excel to automatically redefine a range's address according to how

many
rows and columns are in it.

As you describe things, you would have five columns only, so all your
dynamic data range would have to figure out is how many rows it has.

To
keep
things straightforward, assume that the Area values in column A define

the
number of rows in the data range: if there are 200 valid Area values,

there
are 200 valid sales records. (There are ways to avoid making this
assumption.)

So, on the worksheet where you have put all your data, both the

current
and
the prior, you could define (you need do so once only) a name such as
SalesData, by choosing Insert | Name | Define, typing SalesData into

the
Names in Workbook box, and this formula into the Refers to box:

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

This range definition starts with cell A1 (argument 1) and offsets the

range
by 0 rows (argument 2) and zero columns (argument 3). It offsets the

range
by zero rows because you need to tell Excel the variable/field names

in
Row
1. Because you have one data record for each value in Column A, less 1

for
the variable name in Row 1, Excel makes the range have that many rows,

but
it includes the first row for the variable names, because you have to

pass
those along to your pivot table (see below). And as your data comes to

you,
and after you've pasted the date into column E, the range has five

columns.

Each time you get another set of data, you can copy that data and

paste
it
into this data range, meanwhile copying the date into the associated

cells
in Column E. The range named SalesData will redefine itself when Excel
counts the newly pasted values it finds in Column A.

(BTW, one drawback with this sort of dynamic name is that the name

does
not
appear in the Name Box. I'm in hopes that this longtime oversight will

be
corrected in Excel 12.)

The hard work is now done, and you're about to start saving time, big

time.
Somewhere else -- presumably in the same workbook and, to keep things

neat,
on a different worksheet, you establish a pivot table. The steps

differ
a
bit depending on which Excel version you're using, and I'll assume a

more
recent one. Choose Data | PivotTable and PivotChart Report. In the

pivot
table wizard's first step, choose Microsoft Excel List or Database,

and
accept the default PivotTable report (you can change it to a

chart/report
later, and this is simpler).

In step 2, type the name of the sales data range -- I suggested

SalesData
above, but it can be anything that isn't smutty. Actually, it could be
smutty, but I promised my mother years ago that I'd never base a pivot

table
on a data range that had a smutty name.

In step 3, indicate where you want to put the pivot table.

Then, the pivot table wizard disappears and you drag fields into the

table
layout on the worksheet. You might want to drag Date into the Row

area,
UPC
into the Column area, and Sold into the Data area. Assuming that you

have
exclusively numeric values in the Sold field, it will default to Sum,

and
as
I've described the table's layout you'll get the sum of units sold for

each
date and each UPC.

You can have more row and column fields, though. One arrangement might

have
Sold in the Data area, Date in the Column area, and both Area and

Store
in
the Row area. This would give you a subtotal for store within area.

BTW, you can group on the Date field to force it to show sales by

something
other than week -- month, quarter, year, quarter within year, etc.

This
sort
of thing is why I've always called pivot tables the most powerful

method
of
data analysis and synthesis in Excel.

There's another kind of field in pivot tables, a Page Field. It's a

way
of
selecting a subset of records. If you put, say, Area into a Page

field,
you
can look at your table for a particular Area that you select via a

dropdown.

Also, bear in mind that a pivot table does not automatically react to

the
presence of new data. You'll want to right-click a cell in the table

and
choose Refresh Data from the shortcut menu. Although your dynamic data

range
automatically refreshes itself when new data comes along, the pivot

table
doesn't, and you need to call its attention to the fact that more data

has
come along. (Again, there are ways to automate that, but at the outset

it's
best to keep things straightforward.)

--
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













Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index and Match issues Mo Excel Worksheet Functions 3 May 19th 05 07:16 PM
Match, Index, Vlookup, Large....Help Please [email protected] Excel Worksheet Functions 3 December 15th 04 01:38 PM
Match & Index Phyllis B. Excel Worksheet Functions 2 November 27th 04 03:26 PM
Find a match that;s not exact Phyllis Excel Worksheet Functions 0 November 8th 04 08:12 PM
Vlookup, Index & Match Phyllis Excel Worksheet Functions 1 November 8th 04 06:11 PM


All times are GMT +1. The time now is 02:34 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017