How can I replace the path of the linked spreadsheet in a cell?
 

I have a spredsheet that is intended to gather information from another
spreadsheet. So I have linked the cells. The problem is that for each new
project the linked file is the same name, but in a different folder.

I have an if statement in a cell like the one below.

=IF(BoltEdgeDist=-2147483648,'C:\TeklaStructuresModels\[jointdefaults.xls]Defaults'!$C$21,BoltEdgeDist/25.4)

I need to be able to change "'C:\TeklaStructuresModels" to the current
folder. I am collecting the name of the current folder in a cell but I am
not able to replace "'C:\TeklaStructuresModels" with the cell or range name.

Is this possible?

You may want to learn more about the EXTERNAL LINKS feature in Excel ( can
view video clips on http://www.auditexcel.co.za/othertools.html or click on
EDIT and then LINKS and try it out yourself). This will show you how to
easily change the source of all links from one file (or folder) to another.
This will not work however if you only want some of the cells to change there
link as it is pretty much an all or nothing type feature.

"Norm" wrote:

I have a spredsheet that is intended to gather information from another
spreadsheet. So I have linked the cells. The problem is that for each new
project the linked file is the same name, but in a different folder.

I have an if statement in a cell like the one below.

=IF(BoltEdgeDist=-2147483648,'C:\TeklaStructuresModels\[jointdefaults.xls]Defaults'!$C$21,BoltEdgeDist/25.4)

I need to be able to change "'C:\TeklaStructuresModels" to the current
folder. I am collecting the name of the current folder in a cell but I am
not able to replace "'C:\TeklaStructuresModels" with the cell or range name.

Is this possible?

You could also look at using the INDIRECT function, although I would look at
using Change Links first and see if that does what you need.

=INDIRECT("'"&A1&"\[jointdefaults.xls]Defaults'!$C$21")

Assuming C:\TeklaStructuresModels is in cell A1.

When you change the path, you will of course need to open the source
workbook to update all of the links.

"Norm" wrote:

I have a spredsheet that is intended to gather information from another
spreadsheet. So I have linked the cells. The problem is that for each new
project the linked file is the same name, but in a different folder.

I have an if statement in a cell like the one below.

=IF(BoltEdgeDist=-2147483648,'C:\TeklaStructuresModels\[jointdefaults.xls]Defaults'!$C$21,BoltEdgeDist/25.4)

I need to be able to change "'C:\TeklaStructuresModels" to the current
folder. I am collecting the name of the current folder in a cell but I am
not able to replace "'C:\TeklaStructuresModels" with the cell or range name.

Is this possible?