CODEWITHSUNDEEP
excelvbauser-defined-functions
r z
r z

Reputation: 99

Auto-filling cell string values based on seperate cell values1

I want to create a worksheet where an operator can fill in various info (such as customer, part number, serial number, inspection document #, acceptance criteria #, etc.).

When the operator fills in the part number cell (with a valid value), I want the inspection document # and acceptance criteria # to automatically fill themselves. 

'part number = Shockwave1 (located at A14)
'inspection doc = REV ATZ 2175 (located at H14)
'since part number = Shockwave1, inspection doc is = REV ATZ 2175,
 thus H14 automatically updates to this value when part number is
 selected.

Sub Test()
    If Sheet1.Range("A14").Text = "Shockwave1" Then
        Sheet1.Range("H14").Text = "REV ATZ 2175"
    ElseIf Sheet1.Range("A14").Text <> "Shockwave1" Then
        Sheet1.Range("H14").Text = "_"
Application.ScreenUpdating
End If

I understand I can do this within the excel worksheet using IF/ELSE IF functions (without VBA), but I have 100+ customers and part numbers and each follow different inspection/specification doc #. Code writing would be much easier if I could use vba.

Upvotes: 1

Views: 103

Answers (1)

Brad Lee
Brad Lee

Reputation: 187

I think a VLOOKUP is what you're looking for. Put your part number in column A, inspection document in column B and acceptance criteria and in column C of Sheet2, then in Sheet1 run a VLOOKUP in that reads something like 

=IFERROR(VLOOKUP(A1,Sheet2!B:B,1,FALSE),"No Inspection Document number")

This would bring in the inspection document number. Repeat for the acceptance criteria.

Upvotes: 1

Related Questions