名称/值对的Excel下拉列表 [英] Excel dropdown with name/value pairs

问题描述

我有2个工作表的工作簿。

Sheet2有两列：

  | A | B | 
 + --------- + --------------- + 
 | code1 |描述1 | 
 | code2 |描述2 | 
  

Sheet1有几列，其中一列（D列）是代码 。在这一列中，我需要一个下拉框，什么

• 将显示列Sheet2！B（说明），当用户选择一个描述


• 将从col中输入代码。

在Sheet1中没有附加帮助列可以执行 （Excel 2010）

所以，需要一些html中的简单的东西：

 <选择> 
< option value =code1>说明1< / option> 
< option value =code2> Descr 2< / option> 
< / select> 
  

当用户选择Descr 2时，表单获取code2。

这个问题可能是重复的 - 但我不知道 - 到：

这将是复杂的。您将要做的是（a）当用户选择列D中的一个单元格时，使组合框显示，（b）动态调整框的显示项。它将涉及VBA代码，我不是100％肯定这是可能的。这当然不值得努力。

I have workbook with 2 worksheets.

"Sheet2" has two columns:

|    A    |      B        |
+---------+---------------+
|  code1  | description 1 |
|  code2  | Descr 2       |

Sheet1 has several columns, one of them (column D) is code. In this column i need a "drop box", what

• will show column Sheet2!B (the descriptions), and when the user selects one description
• will enter the code from the col:A.

It is possible to do without additional helper column in Sheet1? (Excel 2010)

So, need something what is dead simple in html:

<select>
  <option value="code1">Description 1</option>
  <option value="code2">Descr 2</option>
</select>

when when the user selects "Descr 2", the form get "code2".

This question probably is an duplicate - but i'm not sure - to: How to create dropdown with multiple columns in excel, but the only answer to it pointing me to an external site where the solution is for another problem.

Added a screenshot for more precise explanation:

解决方案

It sounds like Data Validation (allow List) combined with VLOOKUP will do what you want.

On sheet 2 set up your description/code list. Make it a named range (helps avoid circular reference problems).

On Sheet 1, in the description column, use Data Validation to make dropdown lists that reference the description column of the list. In the code column use the VLOOKUP function, keying off of the dropdown list value.

=IF(B4="", "", VLOOKUP(B4, FruitList, 2, FALSE))

Updated -

I'm starting to see what you mean by "without a helper column", but I'm not sure you can get exactly what you want. A fact of Excel design: what you see is what you get, i.e. the value that displays in the cell is the effective value of that cell. You can't have a cell display one value but "contain" another value. Such a thing is "dead simple" in HTML, but an HTML control isn't built for the same purpose as a cell in a spreadsheet. It's two things at the same time: a value, and a user interface presentation of that value. A spreadsheet cell can contain a way to determine a value (dropdown list, formula, etc.) but whatever value it reaches is going to be the value it shows.

Excel has forms support with things like combo boxes but I believe the value is still output to another cell.

The usual approach to this is to use data validation to create a dropdown list and have a separate column using VLOOKUP for the code. If you really can't have another column to contain the code then I'm not sure what to tell you. It would depend on how the data is to be consumed; are you trying to get a printout, or is the sheet being processed by another program?

Update 2

If you're really bent on not using a separate code column you may be able to use a combo box technique as partially described here:

http://www.contextures.com/xlDataVal10.html

It would be complicated. What you would have to do is (a) get the combo box to show up when the user selects one of the cells in column D, and (b) dynamically adjust the box's display items. It would involve VBA code and I'm not 100% sure it's possible. It certainly doesn't seem worth the effort.

这篇关于名称/值对的Excel下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！