In colunm "A" I have a list that will be filled up by the user; it starts at "A5" but I don't know where it will end.
在colunm“A”中,我有一个将由用户填写的列表;它从“A5”开始,但我不知道它将在何处结束。
In column "B" I'd like to make cells look like dropdown menus where items in the list will be the values from column "A".
在“B”列中,我想让单元格看起来像下拉菜单,其中列表中的项目将是“A”列中的值。
And whenever the user adds a value to column "A" this should be automatically added to the list.
只要用户向列“A”添加值,就应该自动将其添加到列表中。
2 个解决方案
#1
1
Create named range (say colA
) with formula:
使用公式创建命名范围(比如colA):
=$A$5:INDEX($A:$A,MATCH(2,1/($A:$A<>"")))
and then use Data validation with named range colA
:
然后使用命名范围colA的数据验证:
#2
0
In Column A, set up your starting data list as a Table (select your list and then Insert/Table). Give your new table a nice name (select the Table then go to Table Tools/Design/Table Name). Tables in Excel automatically expand when a new value is added to an existing Table.
在A列中,将起始数据列表设置为表格(选择列表,然后选择“插入/表格”)。为新表提供一个好名字(选择Table然后转到Table Tools / Design / Table Name)。将新值添加到现有表时,Excel中的表会自动展开。
Next, select this same starting data list and create a Named Range. Go to Formulas/Define Name. Give your list a Name (for example, "List"), and in Refers To enter your Table name. (e.g. type "=Table1" if that's the name of your Table)
接下来,选择相同的起始数据列表并创建命名范围。转到公式/定义名称。为列表指定名称(例如,“列表”),然后在“引用”中输入表名称。 (例如,如果这是您的表的名称,请键入“= Table1”)
I am assuming that for your Column B, you want Data Validation - it puts the dropdowns in each cell as you suggest. Select the range of cells that you are going to validate, then go to Data/Data Validation. Under Settings, choose Allow = List. Under Source type the name of your Named Range that you set up in Column A. (e.g. type "=List"). Add an Input and Output message to your Validation (optional but helpful). Click OK to close.
我假设对于您的B列,您需要数据验证 - 它会按照您的建议将下拉列表放在每个单元格中。选择要验证的单元格范围,然后转到数据/数据验证。在设置下,选择允许=列表。在“源”下,键入在A列中设置的命名范围的名称(例如,键入“= List”)。在验证中添加输入和输出消息(可选但有帮助)。单击“确定”关闭。
Credit to Chandoo.org where I learned this.
感谢Chandoo.org,我在那里学到了这一点。
#1
1
Create named range (say colA
) with formula:
使用公式创建命名范围(比如colA):
=$A$5:INDEX($A:$A,MATCH(2,1/($A:$A<>"")))
and then use Data validation with named range colA
:
然后使用命名范围colA的数据验证:
#2
0
In Column A, set up your starting data list as a Table (select your list and then Insert/Table). Give your new table a nice name (select the Table then go to Table Tools/Design/Table Name). Tables in Excel automatically expand when a new value is added to an existing Table.
在A列中,将起始数据列表设置为表格(选择列表,然后选择“插入/表格”)。为新表提供一个好名字(选择Table然后转到Table Tools / Design / Table Name)。将新值添加到现有表时,Excel中的表会自动展开。
Next, select this same starting data list and create a Named Range. Go to Formulas/Define Name. Give your list a Name (for example, "List"), and in Refers To enter your Table name. (e.g. type "=Table1" if that's the name of your Table)
接下来,选择相同的起始数据列表并创建命名范围。转到公式/定义名称。为列表指定名称(例如,“列表”),然后在“引用”中输入表名称。 (例如,如果这是您的表的名称,请键入“= Table1”)
I am assuming that for your Column B, you want Data Validation - it puts the dropdowns in each cell as you suggest. Select the range of cells that you are going to validate, then go to Data/Data Validation. Under Settings, choose Allow = List. Under Source type the name of your Named Range that you set up in Column A. (e.g. type "=List"). Add an Input and Output message to your Validation (optional but helpful). Click OK to close.
我假设对于您的B列,您需要数据验证 - 它会按照您的建议将下拉列表放在每个单元格中。选择要验证的单元格范围,然后转到数据/数据验证。在设置下,选择允许=列表。在“源”下,键入在A列中设置的命名范围的名称(例如,键入“= List”)。在验证中添加输入和输出消息(可选但有帮助)。单击“确定”关闭。
Credit to Chandoo.org where I learned this.
感谢Chandoo.org,我在那里学到了这一点。