Click here to Skip to main content
14,332,414 members
Rate this:
Please Sign up or sign in to vote.
I'm trying create dynamic drop down list in MS Excel O365. I've made 2 tables in 2 different sheets. In below are table in "Workers" sheet:

https://i.stack.imgur.com/khSb0.png[^]

And in "Order_status" sheet:

https://i.stack.imgur.com/g5H4I.png[^]

As for as these tables are concerned i inserted that data manualy. Now i'd like create dynamic drop down which in "ID_Worker" i get data from "Workers" sheet and when i select ID_WORKER in "Order_status" sheet:

a) not only displays ID_WORKER, FNAME, LNAME (For example 1 Paul Boy)

b) Automatically writes data into ID_WORKER, FNAME and LNAME columns.

What I have tried:

I've done dynamic drop down list which it selects only ID_Workers from "Workers" sheet. And that source formula i've written like this:

=Workers!$A$2:$A$1048576


I'll explain what i'd like to do (In example):

1) In "Order status" are 3 columns: "ID_WORKER", "FNAME", "LNAME" but the data in this sheet are empty.

https://i.stack.imgur.com/JIIHe.png[^]

2) When i click on cell in "ID_WORKER" column then it shows like this: "1 Paul Boy".

3) Then i select value in this column later in "FNAME" and "LNAME" column should be written automatically: In "FNAME" is "Paul" and in "LNAME" should be "Boy".

I was searching for any solutions but i have still no clue what to do? Any ideas? Thx for any help! :)
Posted
Updated 14-Jul-19 7:59am
v2
Comments
CHill60 14-Jun-19 9:00am
   
All of your links in the question are broken.
You've asked for a dynamic dropdown list but it sounds more like you want other columns automatically populated when you choose an item from the dropdown.
Which is it?
Rate this:
Please Sign up or sign in to vote.

Solution 1

This is simply achieved using VLOOKUP.
For example in cell B2 of your empty sheet put the formula
=VLOOKUP($A2,Workers!$A:$C, 2, 0)
and in cell C2 of your empty sheet put the formula
=VLOOKUP($A2,Workers!$A:$C, 3, 0)
Note that it is better (quicker) to use INDEX and MATCH[^] if you have a lot of columns looking up to the same row.

All you have to do is drag the formulae down the page.

Alternatively you can set up the formulae or clear the contents if a number is deleted by putting some VBA behind the sheet.

Right-click on the sheet that you want to populate and select "View Code".
This will take you to an empty code module for the sheet. Paste in the following code
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
        If Len(Target.Value2) > 0 Then
            Target.Offset(0, 1).Formula = "=VLOOKUP(" & Target.Address & ",Workers!A:C, 2, 0)"
            Target.Offset(0, 2).Formula = "=VLOOKUP(" & Target.Address & ",Workers!A:C, 3, 0)"
        Else
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 2).ClearContents
        End If
    End If
End Sub
Now when you select an item from the drop down in column A, columns B and C will be populated with the first name and last name from the "Workers" sheet. If you delete the contents of a cell in column A then the formulae will be removed from columns B and C.

Beware - this code does not handle a range of cells being changed at the same time, e.g. if you paste a set of values into column A
   
Comments
Maciej Los 17-Jun-19 9:09am
   
5ed!
Rate this:
Please Sign up or sign in to vote.

Solution 3

Ok i've done in different way:

1. I created a table with workers and name it "tblWorkers".
2. I selected the correct range to import the drop down list with the ID_WORKERS, the i went to Data, Data Tools tab, Data Validation, Allow:List & Source:

=INDIRECT("tblWorkers[ID_WORKERS]")


3. There are formulas for

a) Fname:
=IF(E2<>"",IFERROR(VLOOKUP(E2,tblWorkers[#All],COLUMN(tblWorkers[FNAME]),FALSE),"Not Matched"),"")


b) Lname:
=IF(E2<>"",IFERROR(VLOOKUP(E2,tblWorkers[#All],COLUMN(tblWorkers[LNAME]),FALSE),"Not Matched"),"")
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100