是否可以使用VLOOKUP返回公式? [英] Is it possible to use VLOOKUP to return a formula?

问题描述

我在excel 2010工作簿(result_master.xls)中有一个表.A列具有名称，B列具有公式，请参见下面的示例

在另一个工作簿(user_result_sheet.xls)中，我有一个表，该表要使用VLOOKUP从另一个工作簿的表数组中根据单元格$ A $ 1的查找值返回单元格$ B $ 2中的适当公式

result_master.xls中的公式会随着时间而变化，因此我希望能够在一个地方更新它们，而不是更新将在整个公司网络中分布的60多个user_results_sheet工作簿中的每个公式./p>

我的第一次尝试

  = VLOOKUP($ A $ 1，[result_master.xlsx] Sheet1！$ A $ 1:$ B $ 4,2，FALSE) 

返回0(零)，因为它将公式应用于(results_master)工作簿

我需要将公式应用于(user_result_sheet)工作簿

我尝试使用LEFT，MID和TRIM的不同组合来删除对工作簿/工作表名称的引用，但是即使返回了公式的正确部分，它仍然是字符串，然后无法计算.

解决方案

您可以编写一个VBA用户定义的函数:

 函数Eval(以字符串形式)作为变体应用.易失性评估=应用评估结束功能 

然后像这样使用它:

在Sheet1中(请注意，没有前导等号):

然后在Sheet2中:

使用公式 = eval(VLOOKUP(Sheet2！A4，Sheet1！$ A $ 1:$ B $ 4,2，FALSE))输入 B4 并向下复制

I have a table in an excel 2010 workbook (result_master.xls). Column A has Name, Column B has a Formula see example below

In another workbook(user_result_sheet.xls) I have a table where I want to use a VLOOKUP to return the appropriate formula in cell $B$2 from the table array in the other workbook based on lookup value from cell $A$1

The formulas in result_master.xls will change over time, so I wanted to be able to update them in one place rather than update the formulas in each of the 60+ user_results_sheet workbooks that will be distributed all over the company network.

my 1st attempt

=VLOOKUP($A$1,[result_master.xlsx]Sheet1!$A$1:$B$4,2,FALSE)

returned 0(zero), as it applies the formula to the (results_master) workbook

I need it to apply the formula to the (user_result_sheet) workbook

I tried using different combinations of LEFT,MID, and TRIM to remove the reference to the workbook/sheetname but even when that returned the correct part of the formula, it was as a string and then wouldn't calculate.

解决方案

You could write a single VBA user defined function:

Function Eval(s As String) As Variant
    Application.Volatile
    Eval = Application.Evaluate(s)
End Function

then use it like this:

In Sheet1 (note the absence of the leading equals sign):

Then in Sheet2:

With the formula =eval(VLOOKUP(Sheet2!A4,Sheet1!$A$1:$B$4,2,FALSE)) entered into B4 and copied down.

这篇关于是否可以使用VLOOKUP返回公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！