3 dropdown list, update on each other respectively...

[kimyap]

Hi all

I’m newbie to works with excel programming and macros, I hope anybody will help on this…

I’ve created three named range to be used in three dropdown lists respectively from Data Validation menu

Named range 1: Company_A_PartNumber
Named range 2: Company_B_PartNumber
Named range 3: Product_Description

When I choose the Company_A_PartNumber from dropdown list, Company_B_PartNumber will shown the correct part number, and Product_Description also display the corresponding description.

Similar to other two dropdown lists (Company_B_PartNumber and Product_Description), e.g. when I choose part number from Company_B_PartNumber, the correct data will automatically shown in Company_A_PartNumber and Product_Description dropdown lists.

Hope I’m clearly express the situation.

I’m using excel 2007, Thank you for your help!

regards,
kimyap

[aldredd]

Hello, could you not use vlookup?

If need be, apply the vlookup when they select the item...

so, in the sheet code (not a module)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$7" Then FindPart
End sub

Where G7 is the location of the drop down menu.

then somewhere else, use something like this

Sub FindPart ()
range("Company_B_PartNumber").Value = Application.WorksheetFunction.Vlookup (Range("G7"), Range("YOURDATA"),2,0)
End Sub

[kimyap]

Hi aldredd,

Thanks for your reply, basically i 've tried what you have demonstrated to me, i coded as

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$4" Then FindPart
End Sub

Sub FindPart()
    Range("Company_A_PartNumber").Value = Application.WorksheetFunction.VLookup(Range("B4"), Range("List!C2:E4"), 2, 0)
End Sub

Did i put in the wrong function? I've create an excel list with a populated data, It's appreciated if you can take a look and advise me on this...

btw, i save the file as excel 2003 workbook

Thank you so much!
Regards,

Hello, could you not use vlookup?

If need be, apply the vlookup when they select the item...

so, in the sheet code (not a module)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$7" Then FindPart
End sub

Where G7 is the location of the drop down menu.

then somewhere else, use something like this

Sub FindPart ()
range("Company_B_PartNumber").Value = Application.WorksheetFunction.Vlookup (Range("G7"), Range("YOURDATA"),2,0)
End Sub

[aldredd]

Hi Kimyap,

you got the vlookup part slightly wrong..

it should look something like...

Range("C4").Value = Application.WorksheetFunction.VLookup(Range("B4"), Sheets("List").Range("C2:D4"), 2, 0)

You were trying to change the range in the list sheet, rather than the range on the category sheet (in Bold)
You were also referencing the source range incorrectly as well; you used

Range("List!C2:E4")

but to refer to a range in a different sheet, you first specify the sheet, then the range...

Sheets("List").Range("C2:D4")

Once you have that bit, you can add the line to get the second part number...

Range("D4").Value = Application.WorksheetFunction.VLookup(Range("B4"), Sheets("List").Range("C2:E4"), 3, 0)

so your finished sub looks like

Sub FindPart()
    Range("C4").Value = Application.WorksheetFunction.VLookup(Range("B4"), Sheets("List").Range("C2:D4"), 2, 0)
    Range("D4").Value = Application.WorksheetFunction.VLookup(Range("B4"), Sheets("List").Range("C2:E4"), 3, 0)
End Sub

then add another line in your Worksheet_Change event to look for the other changes...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$4" Then FindPart
    If Target.Address = "$C$4" Then FindPart2
End Sub

Now, I can see 2 foreseeable problems..

1. you wont be able to do a vlookup against the second part number to get the first part number or product name, as you can't vlookup to the left. For this, you will need to either do a Index / Match or Offset / Match (both do the same) just search 'Left vLookup' for help on this
2. I don't know what will happen when you add all the other 'worksheet change' bits. Because, when you change the first part code, it changes the cell C4, and if there is also a trigger on this cell, it will try to to do the vlookup and so on, so it may go into a bit of a frenzied loop - I'm not sure. If it does cause a problem, try using application.calculation = xlManual to turn off calculation, and then application.calculation = xlAutomatic to turn it back on once it's done the lookups

[aldredd]

OK, I decided to have a bit of a play with this - to settle my own curiosity you could say

Anyway, I was right in saying you cant lookup to the left - but offset wasn't the answer - but found a similar solution.

More importantly, I was correct about the frenzied looping thing - the code will go on infinity, re calculating everything over & over again.

Luckily, I tinkered, and found the solution!

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$B$4"
            FindPart
        Case "$C$4"
            FindPart2
        Case "$D$4"
            FindPart3
    End Select
End Sub

Ok, this bit is just a tidier way of doing the IF statements.

Sub FindPart()
If Application.ActiveCell.Address = "$B$4" Then
    Range("C4").Value = Application.WorksheetFunction.VLookup(Range("B4"), Sheets("List").Range("C2:D4"), 2, 0)
    Range("D4").Value = Application.WorksheetFunction.VLookup(Range("B4"), Sheets("List").Range("C2:E4"), 3, 0)
End If
End Sub
Sub FindPart2()
Dim r As Long
If Application.ActiveCell.Address = "$C$4" Then
r = Application.WorksheetFunction.Match(Range("C4"), Sheets("List").Range("Company_A_PartNumber"), 0) + 1
    Range("B4").Value = Sheets("List").Cells(r, 3).Value
    Range("D4").Value = Application.WorksheetFunction.VLookup(Range("C4"), Sheets("List").Range("D2:E4"), 2, 0)
End If
End Sub
Sub FindPart3()
Dim i As Long
If Application.ActiveCell.Address = "$D$4" Then
r = Application.WorksheetFunction.Match(Range("D4"), Sheets("List").Range("Company_B_PartNumber"), 0) + 1
    Range("C4").Value = Sheets("List").Cells(r, 4).Value
    Range("B4").Value = Sheets("List").Cells(r, 3).Value
End If
End Sub

Here we have the 2 chucks of code / subs

As you see, the last one uses a formula to find the row you're looking for, and then find the correct match that way.
Most importantly, each sub checks to see if the cell you're working on matches the cell the code relates to, and only continues if this is correct - thus fixing our looping problem!

have fun!

[kimyap]

Yooooh aldredd,

You are the great, making thing simple and easier, the code is running fine... Thank You...

BTW, this is just a stating for my workbook, i'm working on the xls and database now...

Good luck...

OK, I decided to have a bit of a play with this - to settle my own curiosity you could say

Anyway, I was right in saying you cant lookup to the left - but offset wasn't the answer - but found a similar solution.

More importantly, I was correct about the frenzied looping thing - the code will go on infinity, re calculating everything over & over again.

Luckily, I tinkered, and found the solution!

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$B$4"
            FindPart
        Case "$C$4"
            FindPart2
        Case "$D$4"
            FindPart3
    End Select
End Sub

Ok, this bit is just a tidier way of doing the IF statements.

Sub FindPart()
If Application.ActiveCell.Address = "$B$4" Then
    Range("C4").Value = Application.WorksheetFunction.VLookup(Range("B4"), Sheets("List").Range("C2:D4"), 2, 0)
    Range("D4").Value = Application.WorksheetFunction.VLookup(Range("B4"), Sheets("List").Range("C2:E4"), 3, 0)
End If
End Sub
Sub FindPart2()
Dim r As Long
If Application.ActiveCell.Address = "$C$4" Then
r = Application.WorksheetFunction.Match(Range("C4"), Sheets("List").Range("Company_A_PartNumber"), 0) + 1
    Range("B4").Value = Sheets("List").Cells(r, 3).Value
    Range("D4").Value = Application.WorksheetFunction.VLookup(Range("C4"), Sheets("List").Range("D2:E4"), 2, 0)
End If
End Sub
Sub FindPart3()
Dim i As Long
If Application.ActiveCell.Address = "$D$4" Then
r = Application.WorksheetFunction.Match(Range("D4"), Sheets("List").Range("Company_B_PartNumber"), 0) + 1
    Range("C4").Value = Sheets("List").Cells(r, 4).Value
    Range("B4").Value = Sheets("List").Cells(r, 3).Value
End If
End Sub

Here we have the 2 chucks of code / subs

As you see, the last one uses a formula to find the row you're looking for, and then find the correct match that way.
Most importantly, each sub checks to see if the cell you're working on matches the cell the code relates to, and only continues if this is correct - thus fixing our looping problem!

have fun!