Using 'indirect' function with full network path

[Bird222]

I am running Excel 2007. I have a cell (C$110) that contains a path that is used in an 'indirect' function. Currently cell C110 has F:\path to file\.. I would like to change this to \\server\share\path to file\ because someone else that accesses this file has a different map drive letter. They use W instead of F. When I change the path to \\server\share\file\, the indirect formula gives a 'REF#' error. How can I fix this so it works for both of us?

TIA

 

[Mushkins]

Network drive letters in links

When you click a cell containing a link and the source workbook is closed, you'll see the full path to the source workbook. The path uses the drive letter you have mapped to the share. The links continue to work correctly for other users who map different drive letters to the same share.
You can edit linking formulas to use UNC names, such as \\myserver\myshare, instead of drive letters. UNC names can make links easier to update when several people will use a linking workbook, because Excel can update links that use UNC names even if users don't have a drive letter mapped to the network share. For example, you can change
='G:\[source.xls]Prices'!B5
to
='\\myserver\myshare\[source.xls]Prices'!B5

However, using UNC names can also reduce calculation speed, especially if your workbooks use calculation-intensive features such as the Solver add-in program.

https://office.microsoft.com/en-us/...age-links-to-other-workbooks-HA001054812.aspx

Dont forget your single quotes 🙂

 

[Bird222]

https://office.microsoft.com/en-us/...age-links-to-other-workbooks-HA001054812.aspx

Dont forget your single quotes 🙂

Thanks for the response. The problem seems to be that you have to open the relevant workbooks through the UNC path (i.e. \\server\path) instead of the mapped drive letter for the UNC reference to work. Is there a way around this? I.e. I open the files through the mapped drive letter but the spreadsheet still pulls in the cell referenced by the UNC path?

 

[Mushkins]

Thanks for the response. The problem seems to be that you have to open the relevant workbooks through the UNC path (i.e. \\server\path) instead of the mapped drive letter for the UNC reference to work. Is there a way around this? I.e. I open the files through the mapped drive letter but the spreadsheet still pulls in the cell referenced by the UNC path?

It's supposed to do that automatically, though from a technical perspective I have no idea how unless its automatically translating your drive mappings into the explicit UNC paths in excel when you add them to the formula as some sort of hidden variable. I'm kinda not surprised that Technet says "it just works" and it actually doesn't.

Frankly, i'd just start getting into the habit of always using the explicit UNC paths and updating any of your existing workbooks as you go. Seems like a better practice to me since someone looking at the spreadsheet can find the exact path of the second workbook without knowing the ins and outs of someone elses drive mappings.