在Excel中，如何在模板中引用本地工作表？

I am creating my own template which I need to use data from a worksheet called Schedules. The template contains 3 sheets, none of which are the Schedules sheet. This template will only be used in workbooks where the Schedules sheet already exists.

我正在创建自己的模板,我需要使用名为Schedules的工作表中的数据。该模板包含3张纸,其中没有一张是“计划表”。此模板仅用于已存在“计划”表的工作簿中。

My question is, how do I reference a local worksheet which isn't in the template?

我的问题是,如何引用不在模板中的本地工作表?

Whenever I create the template I reference the Schedules sheet but it asks for a file destination. How can I force it to look for a local sheet, even though the sheet won't exist until the template is actually used?

每当我创建模板时,我都会引用“计划”表,但它会询问文件目标。如何强制它查找本地工作表,即使在实际使用模板之前工作表不存在?

1 个解决方案

#1

Depending on how complex your formulas, this could get messy pretty quickly, but one option would be to use the INDIRECT formula.

根据您的公式的复杂程度,这可能会很快变得混乱,但一种选择是使用INDIRECT公式。

Some examples are below:

以下是一些例子:

Gives you the value of A1 on the Schedules sheet =INDIRECT("Schedules!"&"A1")

在计划表上给出A1的值= INDIRECT(“计划!”和“A1”)
Looks up the ID '5' in the range A1:C20 in the Schedules sheet and returns the corresponding value in column B =VLOOKUP(5,INDIRECT("Schedules!"&"A1:C20"),2,0)

在Scheduleles表中的A1:C20范围内查找ID'5'并返回B列中的相应值= VLOOKUP(5,INDIRECT(“Schedules!”和“A1:C20”),2,0)
Multiplies two values on the Schedules sheet =INDIRECT("Schedules!"&"A1")*INDIRECT("Schedules!"&"A2")

将时间表单上的两个值相乘= INDIRECT(“时间表!”和“A1”)*间接(“时间表!”和“A2”)

Once you get your formulas working you can move your templates to the workbook that has the Schedules. All of your formulas will update with appropriate values, and for the record, calculations should be set to automatic.

使公式运行后,可以将模板移动到包含计划的工作簿。所有公式都将使用适当的值进行更新,对于记录,计算应设置为自动。

#1