vba 如何将公式放入数据验证列表 Excel？

How to put formula in Data Validation List Excel?

提问by Chris Aung

I am creating an excel sheet with following Data Validation drop down list.

我正在创建一个带有以下数据验证下拉列表的 Excel 工作表。

1. NA
2. Done
3. (add some formula here)

1. 不适用
2. 完毕
3. （在这里添加一些公式）

Basically, i will be able to select either plain text "NA"/ "Done" from the dropdown list. But sometimes, I want the user to be able to calculate some values based on the cell respective to the row selected so, I want to have one formula as a choice inside the data validation dropdown list. Is this possible?

基本上，我将能够从下拉列表中选择纯文本“NA”/“完成”。但有时，我希望用户能够根据所选行的相应单元格计算一些值，因此，我希望在数据验证下拉列表中选择一个公式。这可能吗？

Data Validation List Source

数据验证列表源

When I click on Formulae option, it should execute the formula with respect to the cells in that Row

当我单击“公式”选项时，它应该针对该行中的单元格执行公式

But currently, the formula that i put in doesn't execute, instead it will just show the whole formula in the cell when activated.

但是目前，我输入的公式不会执行，而是会在激活时在单元格中显示整个公式。

1)How can i make it so that when i select the formula from data validation list, it will execute it instead of filling up the cell with it?

1）如何才能使当我从数据验证列表中选择公式时，它将执行它而不是用它填充单元格？

2)How do i set the formula so that it will be using the cell from the current Row? (for example, if i am using the data validation List in N60, the formula should adapt itself to use the cell (let's say A60?).

2）我如何设置公式以便它使用当前行中的单元格？（例如，如果我在N60 中使用数据验证列表，则公式应自行调整以使用单元格（比方说A60？）。

回答by Mark

I may not be able to help with the second part, but I was seeking an answer to the first and discovered a solution/workaround using Name Manager.

我可能无法帮助完成第二部分，但我正在寻找第一部分的答案，并使用名称管理器发现了解决方案/变通方法。

First, in Formula > Name Manager, create a new reference (the "refers to" will contain whatever formula you are wishing to ultimately display in the validation list. For this example, we use the formula reference "=IF($H54=..." and Name it "UniqueName"

首先，在“公式”>“名称管理器”中，创建一个新引用（“引用”将包含您希望最终显示在验证列表中的任何公式。在此示例中，我们使用公式引用“=IF($H54=. ..”并将其命名为“UniqueName”

Now, we go into Data Validation, Select List, and input the three items we want displayed in the list, with an equals sign preceding our newly named reference: ie. "NA,Done,=UniqueName"

现在，我们进入数据验证，选择列表，并输入我们想要在列表中显示的三个项目，在我们新命名的引用前面加上一个等号：即。“不适用，完成，=唯一名称”

Note: You can't start with the =UniqueName or validation will try to read it all as a formula and fail.

注意：您不能以 =UniqueName 开始，否则验证将尝试将其全部作为公式读取并失败。

This method will allow the user to display "NA", "Done", or "=UniqueName" in the cell; if "=UniqueName" is selected, the cell itself will interpret this as a formula and execute it accordingly, displaying the results of "=IF($H54=...", or whateverelse you have designated to use as a named formula.

此方法将允许用户在单元格中显示“NA”、“Done”或“=UniqueName”；如果选择“=UniqueName”，单元格本身会将其解释为公式并相应地执行它，显示“=IF($H54=...”或您指定用作命名公式的任何其他内容的结果。

If it's too late for yours, I hope this helps someone else who may face a similar problem.

如果对您来说为时已晚，我希望这可以帮助可能面临类似问题的其他人。

回答by Arman

Just going to piggyback off of Mark's response.

只是要捎带马克的回应。

If you really needed your named formula to be the first selection in the list, you can setup your list with a leading comma like so:

如果您确实需要您的命名公式作为列表中的第一个选择，您可以使用前导逗号设置您的列表，如下所示：

,=UniqueName,NA,Done

,=唯一名称，不适用，完成

That worked out for my use, and there was no null item listed in the Data Validation drop down. Hope that helps!

这对我有用，并且数据验证下拉列表中没有列出空项目。希望有帮助！

回答by gemmo

While I think I know what you're trying to say. Why don't you just use an IF formula to evaluate everything instead of selecting a drop down for every row manually. You already had it partially solved using IF. Just need to add the criteria for a "Done" and an "NA"

虽然我想我知道你想说什么。为什么不直接使用 IF 公式来评估所有内容，而不是手动为每一行选择一个下拉列表。您已经使用 IF 部分解决了它。只需要添加“完成”和“不适用”的标准

=if(A1="date","Done",if(A1<"date","NA",if(something else until you have all your catergories))