Reference filename based on Cell contents

[JohnHenry]

Hello all,

I have found a number of threads on this subject, but all point towards using '=indirect()' or a macro. The problem with the indirect function is that I cannot consistently have the referenced file open, hence a #REF error. I also do not want to use a macro due to the volatility of the formulas (sometimes, somewhat randomly chosen formulas change and I do not want to the users of this program to have to update a macro on a daily basis...).

What I have tried:

Method 1
H1 = WorkbookName
="'\\Drive\Folder\Folder2\[" & $H$1 & ".xls]SheetName'!$E$18"
**But this simply returns my entire input as a string.
AND
Method 2
H1 = WorkbookName
='\\Yoc2000\shared\BESTPRICE\2013\04.13\[ & $H$1 & .xls]Best Base Price'!$E$18
**(same line, no quotes) But this gives me an "Update Values" msgbox.

My only thought is since Method 1 returns exactly what I need :
'\\Drive\Folder\Folder2\[WorkbookName.xls]SheetName'!$E$18
only without the "=" in front of the path, to somehow use that...

Any ideas?

Thanks for your time and effort,
John

 

[Asala42]

There's another function called INDIRECT.EXT, from the Morefunc add-in, that will work as INDIRECT but on closed workbooks.

 

[JohnHenry]

Asala,

Thanks for the reply. I have read up on it and the issue is that my files are hundreds (sometimes thousands) of cells big. The added lag time from running on big files from an add-in will be too exacting on the office network. Further, since multiple departments (implying a multitude of different computers - usually on the network, but not always) will need access to the file, it is unfeasible to have to download this add-in on every conceivable computer that will access the file.

Given my constraints, I'm looking for any kind of jimmy-rigged solution that will work. However, at this point, until a knight in shining armor comes galloping out of forest, I'm losing hope on the matter.

John

 

[FormR]

If you don't want to use an add-in and you don't want to use VBA then you are probably out of options.

(btw the morefunc code can be embedded into the file (workbook) - negating the need to install on different computers)

 

[JohnHenry]

FormR,

You may indeed be the knight. I wasn't aware of such a possibility. I will look into it and actually test the lag from using the add-in. Thank you.

John

 

[FormR]

Good luck - let us know how you get on.

 

[JohnHenry]

Thanks for the help guys. My solution is really an avoidance of the problem. I did not have a full understanding of how this program is used in the office (I was merely assigned with cleaning it up and reorganizing it). It can be updated with the source file open (once or twice a day, but in a reasonable way), and then when the file is used by other departments they simply click on "don't update - use last known data" in a pop-up box at the start of the program. Rather silly really, but it certainly accomplishes our use for it. But I will keep in mind indirect.ext for future endeavors that aren't as easily avoidable.

Thanks again,
John

 

[JohnHenry]

.