CLE,
Sounds like your converting a manual system....
I would have a header area on the individual worksheets,
showing the book name, ISBN order from, last price and
current Inventory level and value.
The key to your problem is how to get the current
inventory level and value. If we assume that your header
contains rows 1-5 and you insert a split in the worksheet
(lets call it a stock card), your data is inserted in the
body of the stock card. I would assume there is columns
for inventory received (say column G) and inventory
sold/despatched/placed on sale/otherwise removed (say
column H). Your inventory level becomes the total of
Column G - the total of Column H, or =Sum(g6:g6556) - Sum
(h6:h65536).
Now inventory value is a fair bit more complex, and
depends on the valuation method used, FIFO or WAC (here
in Australia, we don't use LIFO).
As I prefer FIFO, let's deal with that. This is going to
need some VBA coding. We already have the number on hand.
What we need to do is to go to the last receipt entry,
determine the cost per item (put that in the last
purchase price cell) and deduct the number on hand from
the number purchased. If the result is zero or -ve, then
multiply the number on hand by the purchase price to
obtain valuation.
If the result is +ve, then we need to repeat the process
for the previous receipt entry, and so on until the
number on hand has been valued. The value of stock is
then the sum of the individual iterations of the loop.
WAC is a little simpler, you can have the weighted cost
calculated as part of the sheet, you can then use the
subtotal worksheet function with average number function
to find an average cost. Problem with this occurs when
major changes in logistics occur or external factors such
as major currency fluctuations.
Either way.....
HTH, if not email me a small sample at
(e-mail address removed) without the nospam
Steve
-----Original Message-----
I have an Inventory workbook where the main document has
a list of all books and a totals column respectively for
each book. They also want individual worksheets in the
same workbook for each book as well (20 sheets to be
exact). In the individual worksheet is where they want
the removal or addition to inventory to be made and then
to link to the main sheet. They want the main sheet to
list the current total to date with no history (all
history is stored in individual book sheet) for each book
and updated whenever entries are made in the individual
sheets. I know I need to link the cells but I cannot
seem to do it by referencing the most recent entry (or at
least I don't know how). I have tried ranges, vlookup
without any success. Any information you can provide is
greatly appreciated. I am definetly new to the extended
formula functions in Excel.
