Custom functions references to other files.

Hi all, I have created a few "Public Functions" that are stored in a master control file ( a file whose name changes with each new version). These functions need to reference named ranges in various files, and I do not wish to "hard code" these file names into the function nor do I want to have too many arguments when entering the function on the spread sheet. How can I get the function code to automatically look up the required file name irrespective to which workbook is "active" at the time of recalculation?

in the code below the file name is hardcoded in "MastFile" this works very well but I would like to remove the hardcoded statement so as to make the code more portable between different files.

Please help.

Please Login or Register  to view this content.

And how would the function or for that matter the user know which is the required file? You could add another argument to pass the filename but in that case the user would have to know the correct filename of all the possible names.

I have tried to create a named range in the "Client file" that calculates the Master File's name (as can be seen in the comment section following the "MastFile" line but this only works if the client file is the active file. If the model is recalculated from any other file this does not work and returns #Value.
I do not want to create another argument for the user to enter as they can make mistakes.

the issue is that every time a new version is created the user must remember to go and change the hardcoded file name in this module and in other modules, I want to eliminate this.

one of the other functions already has 8 arguments and I want to reduce this.