+ Reply to Thread
Results 1 to 9 of 9

Updating inventory Quantities from invoice...

  1. #1
    Registered User
    Join Date
    02-29-2012
    Location
    Germany
    MS-Off Ver
    Excel 2011 MAC
    Posts
    77

    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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    02-29-2012
    Location
    Germany
    MS-Off Ver
    Excel 2011 MAC
    Posts
    77

    Re: Updating inventory Quantities from invoice...

    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...
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Updating inventory Quantities from invoice...

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

  4. #4
    Registered User
    Join Date
    02-29-2012
    Location
    Germany
    MS-Off Ver
    Excel 2011 MAC
    Posts
    77

    Re: Updating inventory Quantities from invoice...

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

  5. #5
    Registered User
    Join Date
    02-29-2012
    Location
    Germany
    MS-Off Ver
    Excel 2011 MAC
    Posts
    77

    Re: Updating inventory Quantities from invoice...

    Quote Originally Posted by watersev View Post
    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...

  6. #6
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Updating inventory Quantities from invoice...

    If

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

    then

    Please Login or Register  to view this content.
    Last edited by watersev; 04-19-2012 at 04:44 PM.

  7. #7
    Registered User
    Join Date
    02-29-2012
    Location
    Germany
    MS-Off Ver
    Excel 2011 MAC
    Posts
    77

    Re: Updating inventory Quantities from invoice...

    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!

    Quote Originally Posted by watersev View Post
    If

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

    then

    Please Login or Register  to view this content.

  8. #8
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Updating inventory Quantities from invoice...

    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.

  9. #9
    Registered User
    Join Date
    02-29-2012
    Location
    Germany
    MS-Off Ver
    Excel 2011 MAC
    Posts
    77

    Re: Updating inventory Quantities from invoice...

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1