updating data entry

[Guest]

hi guys,
I have a question regarding Excel I hope you'd
be able to help me out:
i'm working on inventory files,I have a receiving
spreadsheet and the main inventory.Is there any way to
update the quantity of the items in the main file when
I receive any new item:

RECEIVING FILE:

col. A col.B col.C
partA 20 2/2/07
partB 5 3/20/07
partC 10 3/25/07
partB 100 3/30/07

MAIN INVENTORY FILE

col.A (availability) colB(qty)
partA 1000
partB 100
partC 10000
I need col.B of main inventory file be updated
automatically
thanks in advance.

 

[Guest]

Is the new quantity a replacement or additive?

In your example should 1000 for Part A become 20 or 1020?

 

[Guest]

that's addetive.the new qty should be added to the inventory.1020.thanx

 

[Guest]

Hi,

Do the following:

1. Make two copies of your receiving File. (i.e copy the content and using
Paste lin the Paste special dialog box, paste them in a different location.

2. Now create a sum funtion to sum the value in Colum B of Main Inventory
File, with the Cell value of the Paste linked file.

This way what happens is whenever a new value is entered, the changes will
be reflected in the paste linked cell and this value will be used to add to
the Main inventory file.

Note: You must have a intermediary postion to store values with disturbing
the Receiving File and the Main Inventory File. I assume you got the gist of
what I am trying to communicate.

Challa Prabhu

 

[Guest]

hi challa,
thanx for the reply.as you may know the process of inventory is additive;in
other words all the receiving are added sequentially.for example if the
availability of partB in inventory is now 100 and we've had two receivings of
5 and 100 the availability now should be 100+5+100=205.in your procedure ahe
very last receiving will be added to inventory,the result would be
200(100+100)!