使用存储在单元格中的文件路径的公式 [英] Formula to use File path stored in a cell

问题描述

我在名为设置"的工作表上有一个单元格A1，具有以下文件路径:

C:\Users\Arun Victor\Documents\New folder\MASTER.xlsm

同一工作簿上的Sheet1具有一堆链接到MASTER.xlsm中各个单元的单元，如下所示:

='C:\Users\Arun Victor\Documents\New folder\[MASTER.xlsm]Employee WOs'!D4

现在，每当文件路径发生变化时，我都必须手动编辑每个单元格.相反，我需要一个公式，该公式使用存储在公共单元格A1中的文件路径并执行如下操作:

='*File path from Sheet-Settings A1* Employee WOs'!D4

解决方案

您正在寻找Indirect().如果单元格A1具有要更改的路径(C:\Users\Arun Victor\Documents\New folder\[MASTER.xlsm]).

在工作表1中，可以将当前为=C:\Users\Arun Victor\Documents\New folder\[MASTER.xlsm]Employee WOs'!D4的那些单元格替换为=Indirect("'"&A1&"Employee WOs'!D4").

这是一个例子.我的桌面上有一个名为"TextBook.xlsx"的工作簿.我希望我的公式返回想要的任何单元格的值，并将其放入单元格C9中.

例如，我可以将C9从A1更改为B1029，D9将返回单元格具有的任何值. (在我的"TextBook.xlsx"中，A1只是说"Cell A1".

注意有关使用Indirect()的信息，您必须打开正在使用的文件，否则会出现错误.换句话说，我打开了TextBook.xlsx，这就是为什么您可以看到结果(单元格A1")的原因.如果我关闭工作簿，则将更改为#REF错误.

I have a Cell A1 on a sheet named 'Settings' with the following file path:

C:\Users\Arun Victor\Documents\New folder\MASTER.xlsm

Sheet1 on the same Workbook has a bunch of cells linked to various cells in MASTER.xlsm like the one shown below:

='C:\Users\Arun Victor\Documents\New folder\[MASTER.xlsm]Employee WOs'!D4

Right now,Whenever there is a change in file path I have to manually edit each and every cell. Instead, I need a formula that uses the File path stored in the common cell A1 and does an operation like this:

='*File path from Sheet-Settings A1* Employee WOs'!D4

解决方案

You're looking for Indirect(). If your cell A1 has the path that you will change (C:\Users\Arun Victor\Documents\New folder\[MASTER.xlsm]).

In your sheet1, those cells which are currently =C:\Users\Arun Victor\Documents\New folder\[MASTER.xlsm]Employee WOs'!D4, can be replaced with =Indirect("'"&A1&"Employee WOs'!D4").

Here's an example. I have a Workbook called "TextBook.xlsx" on my desktop. I want my formula to return the value of whatever cell I want, which I will put in cell C9.

I can change C9 from A1 to B1029 for instance, and D9 will return whatever value that cell has. (in my 'TextBook.xlsx', A1 literally just says "Cell A1".

Note about using Indirect(), you must have the file you're using open, or else you'll get an error. In other words, I have my TextBook.xlsx open, which is why you can see the result ("Cell A1"). If I close the workbook, that will change to a #REF error.

这篇关于使用存储在单元格中的文件路径的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！