根据用户输入自动更新数据验证列表 [英] Automatically updating Data Validation lists based on user input

问题描述

我有一个非常大的数据集(约16k行).我有10个更高级别的块，每个块中有4个类别(每个类别10行)，这些类别使用​​数据验证列表显示每个类别中可用的项目.列表应根据用户输入自动更新.我需要您的帮助的是，我想为每个块使用相同的数据集，并且最好使用最少的计算/大小密集型方法.我整理了一个示例文件，用示例概述了问题.

I have a very large data set (about 16k rows). I have 10 higher level blocks and within each block I have 4 categories (10 rows for each) which use Data Validation lists to show items available in each category. The lists should automatically update based on user input. What I need your help with is that I want to use the same data set for each block and preferably a least calculation/size intensive approach. I have put together a sample file that outlines the issue with examples.

示例文件

谢谢您的帮助.

推荐答案

好的，我已经找到了一些东西，但是这样做可能会很耗时.

Okay, I've found something, but it can be quite time consuming to do.

1. 选择每个单元格范围.例如，对于第一个，选择B3:B18，然后右键单击所选内容.找到命名范围..."，并将其命名为"_FIN_CNY".对所有其他范围重复此操作，并在必要时更改名称.

1. Select each range of cells. For instance, for the first one, select B3:B18 and right click on the selection. Find 'Name a Range..." and give it the name "_FIN_CNY". Repeat for all the other ranges, changing the name where necessary.

选择第一个单元格区域以进行数据验证，然后单击数据验证"，选择选项允许:列表"(您已经拥有)，然后在源代码中输入公式:

Select the first range of cells to get the data validation, and click on "Data validation", pick the option "Allow: List" (you already have it) and then in the source, put the formula:

=INDIRECT($G$4&"_CNY")

$G$4是用户将输入的位置.这随着您更改块而改变.
_CNY是类别.将其更改为第二个类别的_CNY2.

$G$4 is where the user will input. This changes as you change blocks.
_CNY is the category. Change it to _CNY2 for the second category.

单击确定"，应该就是这样.重复其他类别.

Click "OK" and this should be it. Repeat for the other categories.

我已将更新的文件放到了保管箱中，您已经可以看到我了对类别CNY，CNY2和INT的_FIN数据进行了处理，对_GER也进行了处理.您会注意到_GER的INT类别不起作用，这是因为命名范围_GER_INT尚不存在.

I have put an updated file on dropbox where you can see I already did it for the data of _FIN for categories CNY, CNY2 and INT and did the one for _GER as well. You'll notice the category of INT for _GER doesn't work, that's because the Named Range _GER_INT doesn't exist yet.

这篇关于根据用户输入自动更新数据验证列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！