在验证源字段中使用CONCATENATE引用命名表 [英] Using CONCATENATE in the validation source field to refer to a named table

问题描述

我的问题源于我曾经给我的一个任务，这让我失望了。我被给了一个很大的树结构，我需要变成一系列依赖的下拉菜单（或类似的东西）。最终目标是在excel电子表格中形成用户输入的方法。

我正在使用Excel 2010，因此更愿意避免使用VBA。

到目前为止，我已经设法通过在树中的前两代使用一个下拉列表，然后两个连续的vlookup来减少列表的数量。然而，下一代需要回到下拉菜单。

可以使用SUBSTITUTE和CONCATENATE函数作为在excel中引用唯一表名的方法。这样做的目的是填充一个下拉列表验证列表。

例如如果单元格A1：C1定义为：

  A1 =食物
 B1 =水果
 C1 =苹果
 D1 =< Empty，result here> 
  

我应用上述功能：

  D1 = SUBSTITUTE（（CONCATENATE（A1，B1，C1）），，）
  

我会得到输出：

  D1 = FoodFruitApples 
  

现在说我有一个名为FoodFruitApples的列表：

  Granny Smith 
金色美味
粉红色女士
  

如何使用表FoodFruitApples创建一个包含下拉列表的单元格？理想情况下，这将全部包含在单元格D1中，没有任何隐藏的列。

这是我第一次提出一个与excel相关的问题，所以我道歉，如果我的术语和问题结构关闭。谢谢你的帮助。

解决方案

你非常接近。说你的列表的范围/表命名为 FoodFruitApples 。在数据验证窗口中，对于列表值，您可以输入：

  = INDIRECT（SUBSTITUTE（CONCATENATE（A1，B1，C1） ，，））
  

应该指向下拉列表的命名范围/表填充。

My question originates from a task I have been given that has had me stumped. I have been given a large tree structure that I need to turn into a series of dependant dropdown (or something similar). The end goal is to form a means of user input in an excel spread sheet.

I am using Excel 2010 and as such would prefer to avoid VBA.

So far I've managed to cut down the number of lists by using a drop down for the first two generations in the tree and then two successive vlookups. The next generation however needs to move back to a drop down menu.

Is it possible to use the SUBSTITUTE and CONCATENATE functions as a mean of referencing a unique table name within excel. The purpose of this would be to populate a dropdown validation list.

e.g. If cells A1:C1 are defined as:

A1 = Food
B1 = Fruit
C1 = Apples
D1 = <Empty, result here>

And I apply the aforementioned functions:

D1 = SUBSTITUTE((CONCATENATE(A1,B1,C1))," ","")

I will get the output:

D1 = FoodFruitApples

Now say I have a list with the name "FoodFruitApples":

Granny Smith
Golden Delicious
Pink Lady

How could I create a cell containing a drop down list with the table "FoodFruitApples"? Ideally this would all be contained within cell D1 without any hidden columns.

This is my first time posing an excel related question so I apologise if my terminology and question structure is off. Thank you for your help.

解决方案

You're very close. Say that the range / table for your list is named FoodFruitApples. In the data validation window, for list value you can enter:

=INDIRECT(SUBSTITUTE(CONCATENATE(A1,B1,C1)," ",""))

That should refer to the named range / table for the dropdown to be filled with.

这篇关于在验证源字段中使用CONCATENATE引用命名表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！