R
Rob
Inventory Linking
Excel 2004
I have a client for whom I have written a workbook with multiple tabs,
each of which is dedicated to a specific grade or type of meat, from
“as received” to cut, trimmed and aged.
The client takes inventory every Friday and the data from handwritten
sheets by the people who count and weigh inventory are input into the
relevant tab sections for calculation of subtotals, totals and average
weight with flags for above or below expected weight range for the
type of meat.
To get the value of the meat per pound, we started the use of the
workbook by researching the costs of each item and inputting the costs
into the individual sheets. So, while the initial workbook had
separate tabs for purchases, that section was unused on Friday of Week
1.
The following Monday purchases were recorded in the Week 1 purchasing
tabs along with the latest costs for the products so Week 1 could be
used for Week 2.
In between the inventory sections and the purchasing sections is an
Inventory + Purchases (“I+P”) worksheet that includes both purchases
and inventory. That section accepts quantities and costs from last
week’s inventory and from this week’s purchases. This I+P sheet then
develops a weighted average of last week’s inventory costs and this
week’s purchase costs. It is not really “mark to market” but takes
known historical costs and modifies them by the cost of new product.
On Friday morning the Week 1 P+I sheet has costs based upon the Week 1
inventory tabs plus the latest costs from purchasing for the following
week (Monday through Friday morning).
Quantities are cleared from the inventory and purchasing tabs and it
is saved as Week 2. The Week 2 inventory tab sheets are still linked
to Week 1 I+P for costs.
The same process goes on for Week 3. However, after clearing and
saving as Week 3, pricing is still linked to I+P of Week 1 so each of
six tabs must be unprotected, linked to Week 2 I+P and re-protected.
For example, last Friday’s is named Inv VI 030609, and its inventory
items are linked to Inv VI 022709. When we clear the inventory and
purchasing sheets of quantities so the workbook can be saved next
Friday as Inv VI 031309 all of its items are still linked to I+P in
Inv VI 022709 and have to be re-linked to Inv VI 030609.
If I am there or reachable over the Internet, I can readily do this re-
linking. The staff at my client has a great deal of trouble with it,
and usually something goes awry, requiring a lot of time re-doing
things.
I cannot figure out how to automate this with a macro since the re-
link is always to a workbook of a different date.
The only thing I can think of is to make all references to a generic
file name like “Last Week” then save Week 2 as Last Week before
clearing it and saving as Week 3. This is not my favorite solution.
Excel 2004
I have a client for whom I have written a workbook with multiple tabs,
each of which is dedicated to a specific grade or type of meat, from
“as received” to cut, trimmed and aged.
The client takes inventory every Friday and the data from handwritten
sheets by the people who count and weigh inventory are input into the
relevant tab sections for calculation of subtotals, totals and average
weight with flags for above or below expected weight range for the
type of meat.
To get the value of the meat per pound, we started the use of the
workbook by researching the costs of each item and inputting the costs
into the individual sheets. So, while the initial workbook had
separate tabs for purchases, that section was unused on Friday of Week
1.
The following Monday purchases were recorded in the Week 1 purchasing
tabs along with the latest costs for the products so Week 1 could be
used for Week 2.
In between the inventory sections and the purchasing sections is an
Inventory + Purchases (“I+P”) worksheet that includes both purchases
and inventory. That section accepts quantities and costs from last
week’s inventory and from this week’s purchases. This I+P sheet then
develops a weighted average of last week’s inventory costs and this
week’s purchase costs. It is not really “mark to market” but takes
known historical costs and modifies them by the cost of new product.
On Friday morning the Week 1 P+I sheet has costs based upon the Week 1
inventory tabs plus the latest costs from purchasing for the following
week (Monday through Friday morning).
Quantities are cleared from the inventory and purchasing tabs and it
is saved as Week 2. The Week 2 inventory tab sheets are still linked
to Week 1 I+P for costs.
The same process goes on for Week 3. However, after clearing and
saving as Week 3, pricing is still linked to I+P of Week 1 so each of
six tabs must be unprotected, linked to Week 2 I+P and re-protected.
For example, last Friday’s is named Inv VI 030609, and its inventory
items are linked to Inv VI 022709. When we clear the inventory and
purchasing sheets of quantities so the workbook can be saved next
Friday as Inv VI 031309 all of its items are still linked to I+P in
Inv VI 022709 and have to be re-linked to Inv VI 030609.
If I am there or reachable over the Internet, I can readily do this re-
linking. The staff at my client has a great deal of trouble with it,
and usually something goes awry, requiring a lot of time re-doing
things.
I cannot figure out how to automate this with a macro since the re-
link is always to a workbook of a different date.
The only thing I can think of is to make all references to a generic
file name like “Last Week” then save Week 2 as Last Week before
clearing it and saving as Week 3. This is not my favorite solution.