Enter a Corresponding "Customer Code" Value to Cell Found on Separate Worksheet

Invoice Template1.jpg
InvoiceTemplate1
Invoice Template2.jpg
InvoiceTemplate2
CustomerList.jpg
CustomerList

Hey,

I'm having a hard time finding a solution to my issue that I'm hoping one of you might be able to help me with. I'll try to explain the best I can.

Basically, I have a drop down list of all the customers I have on file for my invoicing system (InvoiceTemplate1 picture), and then a similar list of these customers on a second worksheet, but this list contains a matching "Customer Code" that can range from R, L, C, B, Cab, etc. (CustomerList picture). What I want to see happen is, once a customer is selected from the drop down list, the "Code:" cell on the "Invoice Template" worksheet (G4) should automatically display the corresponding customer code found on the other worksheet. This will eliminate the need for any new employees from having to memorise every customer's code. I was thinking that employing some sort of Offset function would take care of this, but I'm drawing a blank.

In an attempt to figure this out myself, I used an Index/Match formula in the cell I40 of the "Invoice Template" worksheet (InvoiceTemplate2 picture). This formula returns the cell location of the matching customer name found in the second "Customer List" worksheet. I was thinking that with this information, one could add an offset to this value, one column to the right, to get the value of the customer code to display. Not sure how to implement this, and the formula in there right now does not include the entire array of customer names found on the second worksheet. You'll see it only searches in Column A2:A34, but with the customer list formatted the way it is (landscape style), the formula gets to be too long, or with "too many arguments" when I just try to Ctrl+Select each column of customer names, so I don't think this formula would help any, but it's there in case it could be used somehow.

Anyway, that's what I would like to see happen. I have blanked out some of the customer names, but you'll get the idea of what I'm trying to accomplish. Let me know if you have any questions. I don't mind using a macro, so long as each time a customer is selected from the drop down, that code cell will update to the knew customer code every time. Thanks in advance for your input,

Matt

Hey,

I have uploaded a bare bones version of my invoice system. You'll see the drop down list for the customer selection in cell B6 in
Invoice Template worksheet. What I want to happen is, when a customer selection is made from the drop down, the customers code is
displayed in cell G4. It should try and find this code on the second worksheet "PST Numbers and Cust List". You will see the same
list of customers there, and in the column beside it, their corresponding customer code. So when the drop down selection is made,
it has to find the matching name in the second sheet, then offset one column to the right to determine the proper customer code to
display in cell G4 on the first sheet. Don't really know how else to describe it than that.

@bulina2k Thank you very much for your suggestion. Unfortunately it did not work. It was giving me a #NAME error. I corrected some
of the cell values to match my worksheet and varied it a few different ways, but it wasn't recognizing what I wanted to do, but I felt
like you were on the right track with it. Maybe take a look at my attached workbook and see if that helps? Thanks a lot.

Thanks all for your input. I await your suggestions.

Matt

That one worked perfect! So simple! Thank you very much for all of your help everyone!

Hey guys,

THANK YOU Bulina2k for your help I haven't tested it yet but maybe it'll help with my next issue!! I'm hoping you guys can still help me! (not sure if this belongs in a new thread or not, but it is pertaining to the same worksheet so here goes.

I will describe my issue.

Using VBA, upon opening the workbook, I have a VBA code that ".clearcontents" of the cells of G4 and F7, and then after it's cleared, I use:

Sheets("Invoice Template").Range("G4").Formula = "=VLOOKUP(B6,'Customer List'!A:B,2,0)
Sheets("Invoice Template").Range("F7").Formula = "=VLOOKUP(B6,'Customer List'!A:C,3,0)

...to put AliGW's code back in, which works awesome. The problem I'm having is, after it does this, I lose all of my data validated drop-down lists in the entire worksheet! So I no longer am able to drop down my customer list in cell B6 and select a customer. It's the strangest thing. I did however once receive an error upon opening the workbook that said something like "automatic update of links has been disabled - Enable Content". I don't know what that means, but I have a feeling it's referring somehow to the link to the 'Customer List' worksheet but I'm not sure. As soon as I delete the above two formulas from my VBA code, my drop down lists return. It's so weird.

I have attached an updated and working version of my workbook for your review. The drop down lists work fine now as I have the above code lines 'commented out. In my VBA code, you can the lines in the picture attached (I hope I made it easy to spot :P) Let me know what you guys think. It's got me perplexed but I have narrowed it down to those two lines of code being the issue here. Thanks a million! I will check back after the weekend!

Matt