This really looks like crap after it gets to the forum. If you like, send
me an email and I'll send you a sample.

Ross

"Ross Culver" wrote in message
...
Looks like this, though you can make it alot neater.

Item Beginning QOH Item Issued Sum of Issued
AA 50 43 AA 4 Item Total
BB 30 26 BB 3 AA 7
CC 25 19 AA 1 BB 4
CC 6 CC 6
AA 2 Grand Total 17
BB 1



"Ross Culver" wrote in message
...
Vandy,

You need to separate your transaction recording from your current status.
And, by the way, you're confusing your terminology. Instock and QOH are
the same thing.

Set a range that indicates the beginning quantities:

item No Beginning Qty
aa 56
bb 30
cc etc.

Keep your transaction range as shown below:

Item No Issued
aa 2
bb 1
aa 3
bb 2

Then in a separate range you need (you could combine this column with the
beginning quantities range to save space):

Item No Beginning Qty QOH
aa 56 here you need a vlookup
to get the total qty issued and then subtract this number from beginning
qty
bb 30
cc etc.

If you're not familiar with vlookups, let me know.

Ross



"vandy" wrote in message
...
I am sorry. I did not ask the question in the proper order.

Once the item is received as instockt the value does not change. The
items
gets issued as per requirments and the QOH has to be calculated for
every
issue made.

How to calculate the qoh and simultaneously update the instock amount to
reflect the qoh to keep it current.


item no Instock Issued Qoh

aa 56 2 54
bb 30 1 29

next time when i issue
item no Instock Issued Qoh

aa 56 3 53
bb 30 2 28

my qoh for the items should be

aa 54-3 = 51
bb 29-2 = 27

how can i do this.


thanks for your patience in reading this msg again!

"Tom Ogilvy" wrote:

In your example, when you issued 4, you changed the 56 to a 54 and
the 2 to
a 4. Then the formula would show 50 which is correct I believe.

--
Regards,
Tom Ogilvy



"vandy" wrote:

Hello Tom,

Thanks for your reply. that is the first thing i tried.

b10 c10 d10
instock issue qoh = instock - issue

56 2 54

next time when i go to issue and change it to 4 it will give me

56-4 = 52 which is wrong.

Should not the qoh = instock qty to make the qoh current. or am i
missing
something here.


"Tom Ogilvy" wrote:

in qoh, why not put a formula

=Instock-issue

so if instock was B10 and issue was C10, D10: =B10-C10

--
Regards,
Tom Ogilvy


"vandy" wrote:

Hi All,

I have a situation were i need to calculate the current stock
inventory of
items.

eg:

item no Instock Issued Qoh

aa 56 2 54
bb 30 1 29

next time when i go to item aa and bb and issue some more items
than it
should update the qoh

eg
qoh. should get updated as instock for the next transaction.

item no Instock Issued Qoh

aa 54 4 40
bb 29 2 27

can this be done. I am not very good with VBA. Any help would be
appreciated.
thanks in advance