Evaluate Method Calling to another Workbook

Hello,
I am trying to use a VBA module function to allow me to pull information from another workbook based on inputs in some cells.
I am trying to do this by trying to have a function that will run a string as a formula.

For example, I have four files:
Calculator.xlsm
\\Server\Front\New.xlsm
\\Server\Front\Old.xlsm
\\Server\Back\New.xlsm
\\Server\Back\Old.xlsm

In Calculator.xlsm I have in my Modules:

Please Login or Register  to view this content.

And in cell C1 I have:

=EVAL(CONCATENATE("'\\Server\",A1,"\[",B1,".xlsx]Sheet1'!A1"))

So that A1 will determine which Folder the file is in, and B1 will determine what the File Name is, and C1 would get the information from whichever file that is.

This doesn't work. From what I can tell and what I read, this method only works on the active Workbook and gives a #REF! error when trying to call to another Workbook like this.

For example, if I simply do the following in Calculator.xlsm cell D2:

=EVAL(CONCATENATE("Sheet",A2,"!",B2,C2))

This will get the information from whichever Sheet number is specified in cell A2, the column specified in cell B2, and the row specified in cell C2 and give the proper output in cell D2, as long as I don't switch to work on another Workbook, for when I do it will draw from the cell identified but in that other Workbook, unless it can't find that a Sheet with that number in that Workbook, in which case I will get a #REF! error.

Is there something I can add to the code to work beyond the active Workbook, or a different method I could use?

I would very much appreciate the help.

Thank you for your time.

Use code in the change event to create a linking formula to the workbook of interest. Below is code that creates links to a chosen workbook - you should be able to modify it easily to do what you want.

Please Login or Register  to view this content.