I have two tables in different sheets. The first one has two columns (Name and Abbreviated Name) and the second one has many columns one of them has data validation list for its cells based on the first column (Name) in the first table. What I want to do is: when I select a value from the drop down list I want it to be replaced with the corresponding value in the (abbreviated Name) column, how I can achieve that?
我有两张不同床单的桌子。第一个列有两列(名称和缩写名称),第二个列有许多列,其中一列根据第一个表中的第一列(名称)为其单元格提供数据验证列表。我想要做的是:当我从下拉列表中选择一个值时,我希望将它替换为(缩写名称)列中的相应值,我该如何实现?
1 个解决方案
#1
This will not do exactly what you ask, but should help you with the purpose, unless that is different from what I can guess.
这不会完全符合你的要求,但应该帮助你达到目的,除非这与我猜的不同。
Create a second column to the right of you column with drop down lists, and make and index/match lookup on you Name and Abbreviated Name table like this: Assuming you have the dropdown lists in col A from row 2 and down it will be entered in B2 and downwards.
使用下拉列表在您的右侧创建第二列,并在您的名称和缩写名称表上进行make和index / match查找,如下所示:假设您在第2行和第2行的col A中有下拉列表,则会输入在B2和向下。
=INDEX(Abbreviated Name;MATCH(A2;Name;0))
Change my ; to , if you language uses , as formula separator. This will result in that you many-columned sheet has an additional row, one for showing abbrebiated name and one for selecting the real name. You could probably do exactly what you want using VBA, if you have at least 1 empty cell in your Name list that you use to create the data validation (allowing you to select values not in the list, but this will make it more complex than it needs to be.
改变我的;如果您使用语言,则作为公式分隔符。这将导致您多列的工作表有一个额外的行,一个用于显示缩写的名称,另一个用于选择真实姓名。如果您在名称列表中至少有一个用于创建数据验证的空单元格(允许您选择不在列表中的值,但这将使其更复杂),您可能可以使用VBA执行您想要的操作。它需要。
#1
This will not do exactly what you ask, but should help you with the purpose, unless that is different from what I can guess.
这不会完全符合你的要求,但应该帮助你达到目的,除非这与我猜的不同。
Create a second column to the right of you column with drop down lists, and make and index/match lookup on you Name and Abbreviated Name table like this: Assuming you have the dropdown lists in col A from row 2 and down it will be entered in B2 and downwards.
使用下拉列表在您的右侧创建第二列,并在您的名称和缩写名称表上进行make和index / match查找,如下所示:假设您在第2行和第2行的col A中有下拉列表,则会输入在B2和向下。
=INDEX(Abbreviated Name;MATCH(A2;Name;0))
Change my ; to , if you language uses , as formula separator. This will result in that you many-columned sheet has an additional row, one for showing abbrebiated name and one for selecting the real name. You could probably do exactly what you want using VBA, if you have at least 1 empty cell in your Name list that you use to create the data validation (allowing you to select values not in the list, but this will make it more complex than it needs to be.
改变我的;如果您使用语言,则作为公式分隔符。这将导致您多列的工作表有一个额外的行,一个用于显示缩写的名称,另一个用于选择真实姓名。如果您在名称列表中至少有一个用于创建数据验证的空单元格(允许您选择不在列表中的值,但这将使其更复杂),您可能可以使用VBA执行您想要的操作。它需要。