Excel依赖下拉列表 [英] Excel Dependent drop down list anwsers

问题描述

我有多个下拉列表依赖于彼此，根据您选择的答案，我希望其他列表被确定。我将尝试解释下面的结构

  **位置** **水果** **位置** **事情* * 
 1香蕉墨西哥电视
 2苹果纽约帽子
 3橙色东京笔
 4葡萄加拿大床
伦敦盒
首页
  

所以例如一个依赖关系是如果你从水果中选择香蕉，那么你只能选择一些

我想完成的是这样的：

如果你从位置选择'1' position =>'1'你唯一的选择从水果将是香蕉，苹果。

  Fruit =>香蕉，苹果。 
如果position = 1（fruit = banana，apple）
  

但是如果你选择'2从位置position =>'2'一切都可以从Fruit获得。但是如果你选择'2'然后香蕉，从'位置'只有加拿大可以从另一个单元格。

 如果position = 2 =>水果：香蕉=>位置=加拿大
如果位置：2 =>水果：苹果=>位置可以等于墨西哥，纽约，东京
  

我在数据表中定义了名称，一个问题我遇到的是定义的名称不能以数字开头。对于一些答案，我绝对需要数字。

我开始写一个论坛，但语法和逻辑不正确。

  = VLOOKUP（$ c4，if（$ c4 =< 1，VLOOKUP（$ c4，MasterList， FALSE），IF（$ C4 =2（$ c4，MasterList，3，FALSE）
  

c4在这种情况下将是最初的下拉列表问题。

我如何才能实现？

解决方案

使用index（）：index（）函数的动态命名范围可以工作，虽然数据需要以这样的方式进行结构化，例如，位置1的水果列表需要：

  AB 
水果
 IndexKey |价值
 1 |香蕉
 1 | Apple 
 ... | ... 
  

将单元格c4作为输入，您可以创建命名范围水果：

  =索引（Sheet2！$ B：$ B，Match（Sheet1！$ C $ 4 Sheet2的$ A：$ A，0））：指数（Sheet2的$ B：$ B，COUNTIF（Sheet2的$ A：！！！$ A，工作表Sheet1 $ C $ 4）+匹配（工作表Sheet1 $ C $ 4 Sheet2！$ A：$ A，0）-1）
  

其中Sheet2是d ata和Sheet1是输入字段表。

然后，您可以为下一个验证列表的Fruits命名范围设置验证列表。每个命名的范围都可以继续依赖于之前选择的选择。

重要的是保持indexkey列的排序，因为匹配如何工作。 b $ b

I have multiple drop down list that are dependent on each other and depending on which answers you choose i would like other lists to be determined. I will try to explain structure below

**Position**      **Fruit**            **Location**    **Thing**       
 1                Banana                 Mexico        TV
 2                Apple                  New York      hat
 3                Orange                 Tokyo         pen
 4                grape                  Canada        bed
                                         London        box
                                         Home

So for example a dependency is if you select 'Banana' from 'Fruit' you are only going to be able to select certain options from Location, and Thing list.

What i want to accomplish is something like this :

if you choose '1' from position; position => '1' your only options from fruit would be banana, apple.

Fruit => Banana, Apple. 
if position = 1( fruit = banana, apple)

But if you choose '2' from position; position => '2' everything would be available from Fruit. But then if you choose '2' then Banana, only Canada from 'location' would be available from another cell.

If position:2 => fruit:banana => Location = Canada
If position: 2 => fruit:apple => Location can equal Mexico, New York, Tokyo

I have defined names in a data sheet, one problem i ran into is that defined names cannot start with numbers. and for some answers i absolutely need numbers.

I started writing a foruma but syntax and logic are not correct.

=VLOOKUP($c4,if ($c4 = "<1",VLOOKUP($c4,MasterList,2,FALSE), IF ($C4= "2"($c4,MasterList,3,FALSE).

c4 in this case would be the initial drop down question.

How can i make this possible?

解决方案

Dynamic named ranges using index():index() functions can work. Though, the data needs to be structured in such a way to facilitate it. For example, fruit list for position 1 would need to be:

   A           B
        Fruit
IndexKey  |  Value
   1      |  Banana
   1      |  Apple
  ...     |   ...

With cell c4 as the input, you can create the named range Fruits:

=Index(Sheet2!$B:$B,Match(Sheet1!$C$4,Sheet2!$A:$A,0)):Index(Sheet2!$B:$B,Countif(Sheet2!$A:$A,Sheet1!$C$4)+Match(Sheet1!$C$4,Sheet2!$A:$A,0)-1)

Where Sheet2 is the data and Sheet1 is the input field sheet.

Then, you can set a validation list to the Fruits named range for the next validation list. Each named range can keep relying on the selection from the previous selection.

The important thing is to keep the indexkey columns sorted because of how match works.

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