posting sales code help

[zendog1960]

I have a sheet, "Item Sales" which I would like to used to adjust my "Inventory Data" sheet.

Here are the current layouts:

"Item Sales" only has 3 columns;
Column A = "Item Code"
Column B = "Item Description"
Column C = "Quantity"

"Inventory Data" has many more columns but only the the first three are relevant to this problem.

Column A = "Item Code"
Column B = "Item Description"
Column C = "On Hand"

What I want but cannot figure out is how to do the following:
When I am in the Item Sale sheet and run the 'Post_Sales' macro, it looks at all the occupied rows then by using the 'Item Code', it matches up then takes the "Quantity" and subtracts it from the "On Hand" value in the "Inventory Data" sheet

So if Item Code 123XYZ show that there are 100 "On Hand" and I sell 25 of them. I mark the sale down in the "Item Sales" sheet. When I press the "Post" button, the new value in the Inventory Data sheet for the Item 123XYZ should now be 75.

I am at a loss on how to make this happen though. Any and all help would greatly be appreciated!

 

[zendog1960]

Here is some sample data:

This is the Item Sales Sheet:

Excel Workbook
	A	B	C
1	Item Code	Item Description	Quantity
2	0001	Item A	6
3	0005	Item E	10
4	0006	Item F	32
Item Sales

Excel 2007

Here is the Inventory Data Sheet:

Excel Workbook
	A	B	C
1	Item Code	Item Description	On Hand Quantity
2	0001	Item A	25
3	0002	Item B	36
4	0003	Item C	125
5	0004	Item D	20
6	0005	Item E	61
7	0006	Item F	72
8	0007	Item G	250
9	0008	Item H	50
10	0009	Item I	3
11	0010	Item J	72
Inventory Data

Excel 2007

So with this data, when I run the code I am needing help with, the items sold, for example Item A sold 6, the inventory on hand value would change to 19. And it would go through the entire Item Sales List and change the on hand values.

I could really use some help here.

 

[zendog1960]

Any Ideas folks?

 

[Titian]

My suggestion is to use the first blank column in INVENTORY DATA file and with an =IFERROR(VLOOKUP... formula, lookup each item on this file against the ITEM SALES file.

When found in Item Sales file the sales quantity will be returned, if there is no match i.e. an error has occurred, you should return the value already on the INVENTORY DATA file.

Copy down the formula. With that column highlighted copy and paste values. Copy the whole column of values and paste over the original values.

This is the manual process, a simple macro could follow those steps.

Hope that helps.