1

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
Arun Noel Victor
  • 37
  • 1
  • 2
  • 5

2 Answers2

1

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.

enter image description here

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.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • A1 os not on Sheet1, its in a different sheet called Settings. Will ` =Indirect("'"&A1&"Employee WOs'!D4") ` work? – Arun Noel Victor Aug 18 '15 at 15:58
  • Haha, sorry, which "A1"? My example A1 (in cell `C9`) is on my example Sheet1. If it's in "Settings", then just change the text in `B9` (in my example) to "Settings" instead. If I understand correctly, the PATH (which will change) is in `Summary!A1`, and your formulas using that PATH are in `Sheet1` (or some non-"Summary" sheet). If so, then in your `indirect()` statement, just make sure the sheet being referred to is `Summary` (either by typing it in the formula where `&B9&` is (replace `B9` with `"Summary"`), or change "sheet1" in my example cell `C9` to `Summary`. – BruceWayne Aug 18 '15 at 16:03
0

In your VBA code, you can use something like: 

= Sheet("Settings").Cells(1,1) & "Employee WOs'!D4"

This way your code will automatically take your changes on cell A1 from your Settings sheet.

ManishChristian
  • 3,759
  • 3
  • 22
  • 50