根据另一列的状态更改Excel下拉选择 [英] Change Excel Drop-down selection based on status of another column

问题描述

我有一个Excel文档，在G列中有一个名称列表，由数字1-4和9999组成，表示项目优先级（它们还有条件代码，用于根据选择更改行的颜色

在H列中，我有一个进度计数器，让用户输入4/25/75/100中的一个。

有没有人知道如何使它如此，如果列H设置为100，它会将G列的状态更改为9999（DD列表中的第五个选择）。

解决方案

最简单的方法是用公式预填列G，例如G2：

  = IF（H2 = 100,9999，）
  

您仍然可以使用公式在单元格顶部应用下拉验证。公式将被从下拉菜单中选择的任何值覆盖。

如果您需要能够在G列中指定一个值，然后仍然自动运行可能会更改为9999以后，您需要使用第3列，它始终包含引用G和H列的公式：

  = IF（H2 = 100,9999，G2）
  

或者你需要写VBA它在Worksheet_Change事件上运行。目标在列H中，值为9999，那么它将更新列G中的单元格的值，与目标相同的行。

I have an Excel doc that has a named list in the "G" column, consisting of numbers 1-4 and 9999 that indicate project priority (they also have conditional code on them that changes the color of the row based on selection.

In column "H" I have a progress counter that lets the user enter one of 4 percentages 25/50/75/100.

does anyone know how to make it so that if column H is set to 100, it would change the status of the "G" column to "9999" (the fifth selection on the DD list.)?

解决方案

Easiest way is to just prefill column G with a formula. E.g. in G2:

=IF(H2=100,9999,"")

You can still apply the drop down validation on top of a cell with a formula. The formula will be overwritten with whatever value is chosen from the drop down menu.

If you need to be able to specify a value in the G column and then still have it automatically change to 9999 later, you'll either need to use a 3rd column that always holds a formula referencing both columns G and H:

=IF(H2=100,9999,G2)

Or you'll need to write VBA that runs on the Worksheet_Change event. It the Target is in column H and has a value of 9999, then it will update the value of the cell in column G and the same row as the Target.

这篇关于根据另一列的状态更改Excel下拉选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！