如何制作动态下拉列表? [英] How to make dynamic drop down list?

问题描述

我正在尝试在MS Excel O365中创建动态下拉列表.我在2张不同的纸上做了2张桌子.以下是工作人员"工作表中的表格:

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:

在订单状态"表中:

关于这些表，我手动插入了该数据.现在，我想创建动态下拉列表，该下拉列表在"ID_Worker"中从"Workers"工作表中获取数据，并在"Order_status"表中选择ID_WORKER:

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)不仅显示ID_WORKER，FNAME，LNAME(例如1 Paul Boy)

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

b)自动将数据写入ID_WORKER，FNAME和LNAME列.

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

我已经完成了动态下拉列表，它从"Workers"表中仅选择了ID_Workers.我写的那个源公式是这样的:

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)在订单状态"中有3列:"ID_WORKER"，"FNAME"，"LNAME"，但此工作表中的数据为空.

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

2)当我单击"ID_WORKER"列中的单元格时，它显示如下:"1 Paul Boy".

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

3)然后，我稍后会在此列中的"FNAME"中选择值，"LNAME"列应自动写入:在"FNAME"中是"Paul"，在"LNAME"中应该是"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! :)

推荐答案

您可以尝试:

1. 使用worker创建一个表，并将其命名为"tblWorkers".例如，表范围是 Sheet1 A1:C5 .
2. 使用ID_WORKERS，转到数据"，数据工具"标签，数据验证"，允许:列表&来源: = INDIRECT("tblWorkers [ID_WORKERS]").例如，使用的范围是 Sheet1 E2:E5 .

3. 公式:

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)，不匹配")，")例如，范围是 Sheet1 F2:F5

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)，不匹配")，")例如，范围是 Sheet1 G2:G5

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

图片

这篇关于如何制作动态下拉列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！