如何将公式放在数据验证列表Excel中? [英] How to put formula in Data Validation List Excel?
问题描述
我正在使用以下数据验证下拉列表创建一个Excel工作表.
I am creating an excel sheet with following Data Validation drop down list.
- 不适用
- 完成
- (在此处添加一些公式)
基本上，我将能够从下拉列表中选择纯文本"NA"/完成".但是有时候，我希望用户能够根据所选择的行所对应的单元格来计算一些值，因此，我希望在数据验证下拉列表中选择一个公式作为选择.这可能吗?
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?
数据验证列表来源
当我单击公式"选项时，它应针对该行中的单元格执行公式.
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)我该如何做，以便当我从数据验证列表中选择公式时，它将执行它而不是用它填充单元格?
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?
2)如何设置公式，以便它将使用当前行中的单元格? (例如，如果我使用的是 N60 中的数据验证列表，则公式应适合使用该单元格(例如 A60 ?).
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?).
推荐答案
我可能无法为第二部分提供帮助，但是我正在寻找第一部分的答案，并且发现了使用名称管理器的解决方案/解决方法.
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.
首先，在公式">名称管理器"中，创建一个新引用(引用"将包含您希望最终显示在验证列表中的任何公式.对于此示例，我们使用公式引用"= IF($ H54 = ..."，并将其命名为"UniqueName"
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"
现在，我们进入数据验证"，选择列表"，然后输入要在列表中显示的三个项目，在我们新命名的引用之前加上等号:即. "NA，Done，= 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"
注意:您不能以= UniqueName开头，否则验证将尝试将其全部读取为公式并失败.
Note: You can't start with the =UniqueName or validation will try to read it all as a formula and fail.
此方法将允许用户在单元格中显示"NA"，"Done"或"= UniqueName"；如果选择了"= UniqueName"，则单元格本身会将其解释为公式并执行相应的操作，并显示"= IF($ H54 = ..."或任何您指定用作命名公式的结果.
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.
如果对您来说太迟了，希望对其他可能遇到类似问题的人有所帮助.
If it's too late for yours, I hope this helps someone else who may face a similar problem.
这篇关于如何将公式放在数据验证列表Excel中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
