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

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!

Thanks for your input.
I have seperate worksheets for each supplier with their products.
Are you saying you would prefer to see all items/suppliers in one master
inventory worksheet? We have approx. 125 items

We don't keep track of "detailed" transctions, i.e. we just keep a sales
summary in another workbook that lists, date/cust/invoice#/amt of
invoice/blank col to check off when paid.
I do monthly and qrtly summary and a final at end of year.
How would you structure the master inventory list to take advantage of all
the benefits of excel?
I have read about pivot tables and combo boxes all sound great, with tons of
options availa for sales analysis.
thnx again

"smartin" wrote:

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!

Well there is something of a dichotomy here, at least for me. At one
extreme I think of this project and how I would approach it from the
ground up, applying what I think I know of data modeling and so on to
build an application that serves the requirements both in the present
and the future.

On the other hand, you have something that works to a respectable
degree, and scrapping everything to start over for the sake of a perhaps
smallish future gain may well not be cost-effective.

So let's see if a balance can be had? Let me put this question to you:
Your original question was how to maintain inventory numbers. In your
sales records, are you keeping tallies on quantities of items sold/shipped?

To your other questions, here are some things to think about. I'm not
challenging your approach, just offering views on the situation.

Product/inventory list: why separate products on worksheets? A product
is a product is a product. /One/ distinguishing factor is the supplier,
but so are description, cost, margin, size, weight, etc. Supplier is
just one more column (or "dimension", as we might say) to add. The
advantage of having all the products in one place is having /one/ list
to update and /one/ list to consult when you are querying for a price.
And, lest I fail to mention, /one/ place to keep track of inventories.

Transactional data: This gets down the the fundamental level of doing
business and so is a mainstay. The transaction record says we sold x
quantity of product y at price p on date d to customer c on invoice i.
From this detail a lot of information can be deduced: How many product
y were sold this year? What was the revenue? What was the value of
invoice i? How much did customer c purchase this year versus last year?
What products were profit leaders this year? Last year? And, knowing how
many y were reduced from our inventory is pivotal to knowing what
remains... offset of course by a similar transactional log of receipts.

I am officially rambling now so will sign off... hope this helps!

Michelle wrote:
Thanks for your input.
I have seperate worksheets for each supplier with their products.
Are you saying you would prefer to see all items/suppliers in one master
inventory worksheet? We have approx. 125 items

We don't keep track of "detailed" transctions, i.e. we just keep a sales
summary in another workbook that lists, date/cust/invoice#/amt of
invoice/blank col to check off when paid.
I do monthly and qrtly summary and a final at end of year.
How would you structure the master inventory list to take advantage of all
the benefits of excel?
I have read about pivot tables and combo boxes all sound great, with tons of
options availa for sales analysis.
thnx again

"smartin" wrote:

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!

Smartin;
I am trying to re-structure (play) with a new system before our new fiscal
year May 1st., so I have some time to set this up.

Our sales records do not show a tally of items qty's etc., just a lump sum
total for the invoice.
I understand how a detailed summary would give us more information to play
with.
That is my next of many steps. Is this what is called data tracking add-in?
This is why I am enquiring, because of the need to know more.

You are right the seperate sheets for inventory are for different suppliers.
It would be no big deal to put all on one worksheet with an extra column for
suppliers.

How would I be able to write up an invoice and have that "Transactional
data" automatically transferred to a detailed report/worksheet? without
re-entering all the information, ie. cust/prod sold/qty/value etc? AS WELL AS
reducing the inventory level?


any thoughts?
Thanks again,
Michelle


"smartin" wrote:

Well there is something of a dichotomy here, at least for me. At one
extreme I think of this project and how I would approach it from the
ground up, applying what I think I know of data modeling and so on to
build an application that serves the requirements both in the present
and the future.

On the other hand, you have something that works to a respectable
degree, and scrapping everything to start over for the sake of a perhaps
smallish future gain may well not be cost-effective.

So let's see if a balance can be had? Let me put this question to you:
Your original question was how to maintain inventory numbers. In your
sales records, are you keeping tallies on quantities of items sold/shipped?

To your other questions, here are some things to think about. I'm not
challenging your approach, just offering views on the situation.

Product/inventory list: why separate products on worksheets? A product
is a product is a product. /One/ distinguishing factor is the supplier,
but so are description, cost, margin, size, weight, etc. Supplier is
just one more column (or "dimension", as we might say) to add. The
advantage of having all the products in one place is having /one/ list
to update and /one/ list to consult when you are querying for a price.
And, lest I fail to mention, /one/ place to keep track of inventories.

Transactional data: This gets down the the fundamental level of doing
business and so is a mainstay. The transaction record says we sold x
quantity of product y at price p on date d to customer c on invoice i.
From this detail a lot of information can be deduced: How many product
y were sold this year? What was the revenue? What was the value of
invoice i? How much did customer c purchase this year versus last year?
What products were profit leaders this year? Last year? And, knowing how
many y were reduced from our inventory is pivotal to knowing what
remains... offset of course by a similar transactional log of receipts.

I am officially rambling now so will sign off... hope this helps!

Michelle wrote:
Thanks for your input.
I have seperate worksheets for each supplier with their products.
Are you saying you would prefer to see all items/suppliers in one master
inventory worksheet? We have approx. 125 items

We don't keep track of "detailed" transctions, i.e. we just keep a sales
summary in another workbook that lists, date/cust/invoice#/amt of
invoice/blank col to check off when paid.
I do monthly and qrtly summary and a final at end of year.
How would you structure the master inventory list to take advantage of all
the benefits of excel?
I have read about pivot tables and combo boxes all sound great, with tons of
options availa for sales analysis.
thnx again

"smartin" wrote:

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!

Hi Michelle,

I am not familiar with "data tracking add-in", but it might be a synonym
for something else.

Invoices could (and in the purest sense should) be built from the
transactional data. Not the other way around. Plus, the transactional
data can ideally take care of adjusting inventory stats and other
reporting needs.

If you like, send me a mock-up of your data.

Michelle wrote:
Smartin;
I am trying to re-structure (play) with a new system before our new fiscal
year May 1st., so I have some time to set this up.

Our sales records do not show a tally of items qty's etc., just a lump sum
total for the invoice.
I understand how a detailed summary would give us more information to play
with.
That is my next of many steps. Is this what is called data tracking add-in?
This is why I am enquiring, because of the need to know more.

You are right the seperate sheets for inventory are for different suppliers.
It would be no big deal to put all on one worksheet with an extra column for
suppliers.

How would I be able to write up an invoice and have that "Transactional
data" automatically transferred to a detailed report/worksheet? without
re-entering all the information, ie. cust/prod sold/qty/value etc? AS WELL AS
reducing the inventory level?


any thoughts?
Thanks again,
Michelle


"smartin" wrote:

Well there is something of a dichotomy here, at least for me. At one
extreme I think of this project and how I would approach it from the
ground up, applying what I think I know of data modeling and so on to
build an application that serves the requirements both in the present
and the future.

On the other hand, you have something that works to a respectable
degree, and scrapping everything to start over for the sake of a perhaps
smallish future gain may well not be cost-effective.

So let's see if a balance can be had? Let me put this question to you:
Your original question was how to maintain inventory numbers. In your
sales records, are you keeping tallies on quantities of items sold/shipped?

To your other questions, here are some things to think about. I'm not
challenging your approach, just offering views on the situation.

Product/inventory list: why separate products on worksheets? A product
is a product is a product. /One/ distinguishing factor is the supplier,
but so are description, cost, margin, size, weight, etc. Supplier is
just one more column (or "dimension", as we might say) to add. The
advantage of having all the products in one place is having /one/ list
to update and /one/ list to consult when you are querying for a price.
And, lest I fail to mention, /one/ place to keep track of inventories.

Transactional data: This gets down the the fundamental level of doing
business and so is a mainstay. The transaction record says we sold x
quantity of product y at price p on date d to customer c on invoice i.
From this detail a lot of information can be deduced: How many product
y were sold this year? What was the revenue? What was the value of
invoice i? How much did customer c purchase this year versus last year?
What products were profit leaders this year? Last year? And, knowing how
many y were reduced from our inventory is pivotal to knowing what
remains... offset of course by a similar transactional log of receipts.

I am officially rambling now so will sign off... hope this helps!

Michelle wrote:
Thanks for your input.
I have seperate worksheets for each supplier with their products.
Are you saying you would prefer to see all items/suppliers in one master
inventory worksheet? We have approx. 125 items

We don't keep track of "detailed" transctions, i.e. we just keep a sales
summary in another workbook that lists, date/cust/invoice#/amt of
invoice/blank col to check off when paid.
I do monthly and qrtly summary and a final at end of year.
How would you structure the master inventory list to take advantage of all
the benefits of excel?
I have read about pivot tables and combo boxes all sound great, with tons of
options availa for sales analysis.
thnx again

"smartin" wrote:

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!