How to use a cells value in a filename (VBA)

[Greg_2]

I was wondering if it is possible to use a value that the user inputs into a cell, in a filename for linking to another spreadsheet in VBA.

For example, if the user enters "C-11838" in cell B4, I want to be able to set the forumla in C4 to ='K:\GMG\Nets on Floor\Finished Nets\[C-11838.xlsm]Net Summary'!$B$4

I can't figure out how to set it up so that the number before .xlsm can be a variable.

Any help would be greatly appreciated!

[Domski]

Hi,

Try:

=INDIRECT("'K:\GMG\Nets on Floor\Finished Nets\["&C4&".xlsm]Net Summary'!$B$4")

INDIRECT will not work if the other file isn't open though although I think you can install the MoreFunc add-in and use INDIRECT.EXT

Dom

[Greg_2]

Thanks! It works like a charm when the file is open, though I was hoping there was a way to do this without the file being open.

The ultimate goal of this tool is so that a manager can enter in multiple tag numbers, and have all the values in B4 returned from each file, without them having to open each individual file.

Edit: I'm looking into MoreFunc.

[Greg_2]

MoreFunc Add in works perfectly.

Thank you for your help!