Updating inventory Quantities from invoice...

I have a document with an invoice and inventory on separate worksheets and what I need is a Macro tied to the button in the top right corner with the red text to update the quantities on my inventory worksheet for all the products used on the inventory. Basically it would have to look up each part number "the yellow cells" and subtract the quantity used "orange cells" from the "Fahrzeug Inventar" worksheet...

Arbeitsauftrag = Invoice
Fahrzeug Inventar = Vehicle Inventory

Orange Cells = Quantity
Yellow Cells = Part number

Ok, Maybe I didn't describe this well enough or the workbook was too hard to work with in a different language...

I made a new workbook "in English" so you can see what I need...

In this workbook is an inventory and invoice. I need to have the button on the Invoice update the inventory quantities to reflect the new quantities. I know how to do this with a sunif formula but inorder to maintain some of the other functions of this workbook I would rather do this with a macro.

I have created names in the name manager and data validation on the invoice. this is set up similar to the actual workbook I need this for.

If someone could help me with this I would greatly appreciate it...

hi muddbog, please check attachment. I've added data validation for numbers in Quantity field.

Perfect! Exactly what I was looking for! Thank you very much!

Originally Posted by watersev

hi muddbog, please check attachment. I've added data validation for numbers in Quantity field.

I am having a problem with putting this in to my real invoice... can you see if you can apply this same function to my first attachment? Its not working for me in the other file... when I transfer this code to my real invoice I change the names for the worksheets and the offset numbers, but it doesn't work... I don't know...

Thank you very much... I'm sorry to be a PITA I just figured I would have no problem to transfer it over to the real one... Can I ask how you came up with 10 for the offset? I tried 4 then I thought maybe the numerous cells I merged were the problem so I counted out and came up with 14 and that didn't work either... I am trying to learn too and not just be a leach... I do appreciate everyone on this forum helping me as much as you all have... Words can't describe how thankful I am!

Originally Posted by watersev

If

Service invoice sheet = sheet "Arbeitsauftrag"
Inventory sheet = sheet "Fahrzeug Inventar"

then

Please Login or Register  to view this content.

if cells are not merged: A1 offset(,2) will give you C1. When cells are merged offset starts from the right-most cell within merged, in your file after AQ cell. It takes 10 cells to get left-most merged cell with value.

If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

I will definitely have to remember that... Thank you again
THREAD SOLVED