How to make dynamic drop down list?

Question

0.00/5 (No votes)

See more:

, +

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 3-Jun-19 21:13pm

Member 10696161

Updated 14-Jul-19 7:59am

Maciej Los

v2

Add a Solution

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?

2 solutions

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

VB

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

Posted 17-Jun-19 3:05am

CHill60

Comments

Maciej Los 17-Jun-19 9:09am

5ed!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 10696161 · Accepted Answer · 2019-07-14T07:59:00

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"),"")