CODEWITHSUNDEEP
excellistdynamic
Prochu1991
Prochu1991

Reputation: 453

How to make dynamic drop down list?

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:

enter image description here

And in "Order_status" sheet:

enter image description here

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.

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.

enter image description here

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! :)

Upvotes: 0

Views: 459

Answers (3)

Error 1004
Error 1004

Reputation: 8220

You could try:

  1. Create a table with workers and name it "tblWorkers". For example purposes table range is Sheet1 A1:C5.
  2. Select the range you want to import the drop down list with the ID_WORKERS, Go Data, Data Tools tab, Data Validation, Allow:List & Source: =INDIRECT("tblWorkers[ID_WORKERS]"). For example purposes range used is Sheet1 E2:E5.

  3. Formula for:

    FNAME:=IF(E2<>"",IFERROR(VLOOKUP(E2,tblWorkers[#All],COLUMN(tblWorkers[FNAME]),FALSE),"Not Matched"),"") For example purposes range is Sheet1 F2:F5

    LNAME:=IF(E2<>"",IFERROR(VLOOKUP(E2,tblWorkers[#All],COLUMN(tblWorkers[LNAME]),FALSE),"Not Matched"),"") For example purposes range is Sheet1 G2:G5

Image

enter image description here

Upvotes: 1

Kumaravel K
Kumaravel K

Reputation: 1

Select a cell where you want to create the drop-down list. Go to Data –> Data Tools –> Data Validation.

enter image description here

Upvotes: 0

Xlsx
Xlsx

Reputation: 175

You could use the field ID_Worker in sheet "Order_status" as a key, and lookup values of FNAME & LNAME from the "Workers" table.

Let's say your structure is as below: Sheet "Workers" Cell A1: 1, B1: Paul, C1: Boy Cell A1: 2, B1: Adam, C1: John Cell A1: 3, B1: Martin, C1: Ricky Cell A1: 4, B1: Adam, C1: King

You already have your dropdown in field ID_Worker, sheet "Order_status" (btw the way you're doing it will cause alot of blanks, best to limit it referencing to the total rowcount)

Input the formula into cell b2, sheet "Order_status":=iferror(vlookup($A2,Workers!$A:$C,2,0)) drag the formula down

Repeat the same for cell c2:=iferror(vlookup($A2,Workers!$A:$C,3,0)) drag the formula down

Upvotes: 0

Related Questions