Results 1 to 2 of 2

Thread: Not sure if I need Macro help with this or not...just need help!

  1. #1

    Not sure if I need Macro help with this or not...just need help!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a 2 sheet workbook with data on 2nd sheet. The 1st sheet is an order form. What I need to be able to do on the order form sheet is format all cells from C10 down to C55 so that when you start typing an item# from column A on the 2nd sheet, it automatically generates a list of item#'s that corresponds with it. (Ex. You start typing in the item code for Dogs Playing Poker N/L which is 3DOGPK. The 3DOG is common with the 3DOG for Dogs Playing Poker N/L. What I would like is a list to generate with these 2 item #'s where you can use the arrow down button to select which one you want, hit enter and bam...your item number displays in Order Form C10 PLUS (here's the tricky part) the corresponding description for that item from sheet 2 automatically displays on the order form AND the price from sheet 2 comes over (my formula to calculate by the qty ordered is already entered). Hopefully I will find some help on here because I'm hoping this is not so involved that I have to pay somebody to finish it up. Thanks so much! I attached the sheet im using.
    Attached Files Attached Files

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    No Macros required.

    Unfortunately Excel does not have a handy Autocomplete function. I suppose this could be written in a macro. Done a bypass to get this to work (see later)

    On your sheet 2 I have created two named ranges, Item (column A) and Stock (column (A to D) to make it easier
    Then on the order form, add a data validation to the item Source "=Item"
    Description we could use the VLOOKUP but I chose Match and Index. =IF(C523<>"",INDEX(Stock,MATCH(C523,Item),2),"")
    and Unit price (column 3) =IF(C523<>"",INDEX(Stock,MATCH(C523,Item),3),"")

    Briefly if nothing show nothing else match Item# in Item and return from the Stock based on the matched row and column

    Ok now the hidden bit. Row 11 to 522 is hidden, they contain a copy of the order items. What gives?
    Excel has an Autocomplete (may need to set this in options, google it) BUT it must be on the cells just above with no blank spaces hence the copy over. Now when you start typing it's automatically completed for you.

    I would suggest bringing the whole of sheet 2 into the hidden rows
    Attached Files Attached Files

Posting Permissions

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