IF there is a customer # in cell D2 on the main worksheet I need it to pull the customer's name from the Customer Base Price Sheet (worksheet within the same workbook) the customer's name is located in column B next to the customer's # on the customer base price sheet worksheet.
I am needing it to automatically give my worksheet the customer's name whenever I enter in the customer's number.
Pulling information from other worksheets in a workbook
-
-
Re: Pulling information from other worksheets in a workbook
Hi Alayna and welcome,
I couldn't decide whether you just wanted the customers name returned in a cell next to the customer # or whether you actually wanted to change the sheets name.
So I did both
QuoteI am needing it to automatically give my worksheet the customer's name
To do a lookup in a cell use
CodeE2: =VLOOKUP(D2,'Customer Base Price'!A2:B11,2)In order to change the sheetname you will need to use some VBA code.
Add this to the code module of the sheet you want to change.Code
Display MorePrivate Sub Worksheet_Change(ByVal Target As Range) Dim rngCust As Range If Not Intersect(Target, Range("D2")) Is Nothing Then If Target.Text <> "" Then Set rngCust = Worksheets("Customer Base Price").Range("A:A").Find(Target.Text, LookIn:=xlValues) If Not rngCust Is Nothing Then Worksheets(Target.Parent.Name).Name = rngCust.Offset(0, 1) End If End If End If End SubIf you are changing sheet names you will have to careful that the customer names do not contain any illegal characters.
-
Re: Pulling information from other worksheets in a workbook
The quickest way would be to change the layout of your table
A2 B2 C2
#1 10001 New Customer
#3 41276 BargainsCode=VLOOKUP(D2, 'CUST BASE PRICE SHEET'!A2:B11,3)If you can not change the layout of the table then use this revised formula.
Code=OFFSET('CUST BASE PRICE SHEET'!C2,MATCH( D2, 'CUST BASE PRICE SHEET'!C2:C11,0)-1,-1,1,1)
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!