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

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

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

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

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

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

I'm sorry. I left out something.

You can't get the resulting QOH with the vlookup. You should create a pivot
table to get the total quantites issued, and then use a vlookup to tie that
back to your Items range by subtracting the vlookup result from the
beginning quantity.

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