Michelle wrote:
Is there an easier way to update inventory on a daily basis using Excel from
an invoice? (I am using office/excel 2002)

I have read up on VLOOKUP but can't get my head around it. And it seems I
would have to create a formula for every field/item etc.

Up to now I have a price page that I copy and paste items into an invoice,
which has a blank column I fill in the qty and gets totalled.
I would like the inventory to be adjusted down each time (simply). I
currently have all information on seperate worksheets (ie price pages,
inventory, invoice).
Your help is greatly appreciated.

Hi Michelle,

You are off to a good start keeping things on separate worksheets. If I
were keeping track of inventory, I would have a master "catalog" page
that lists all the items, one per row, starting inventory counts, and a
/calculated/ current inventory number.

Calculated from what, you might ask? Well, the transaction history. I'm
not sure you are capturing this. This would be another worksheet where
you keep track of invoice#/po# and date, item id, quantity ordered,
price, etc., in an ever-growing list. Maybe (but just maybe) your
invoice page(s) capture this, but I can't tell from your description.
Even if you have this kind of information stored per-invoice, it will be
difficult to accumulate if it's not all in one nice table.

You mentioned copy/pasting from the price page and pains with VLOOKUP.
To be successful with this project I you will need to be comfortable
with VLOOKUP, SUMIF, COUNTIF, and likely a few other handy ways to grab
and summarize data in various ways. The effort you put into these basic
functions now will pay dividends later when you see how much automation
can be achieved.

Hope this gives you some ideas. Write back if you have any questions.

Good luck!