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.
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
Bookmarks