找到你要的答案

Q:Replace selected value from data validation list with another value

Q:用另一个值替换数据验证列表中的选定值

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?

我有两张桌子在不同的表。第一个有两个列(名字和缩写名),第二个列有许多列,其中一个有第一个列(名字)在第一个表中的单元格的数据验证列表。我想做的是:当我从下拉列表中选择一个值,我希望它被替换为相应的值(缩写名称)列,我如何能做到这一点?

answer1: 回答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.

=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.

这不会完全符合你的要求,但应该帮助你达到目的,除非这与我所猜测的不同。

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.

=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.

excel  validation