Path of a linked workbook stored as a name

[Mcstefan]

Hi,

I have a master workbook that contains over one hundred worksheets and about 10 other workbooks that are linked to the master. All the linked workbooks are moved from the current month folder to the next month folder. The 10 workbooks are changing the name every month and I want to be able to change the source of the linked files every month. The formulas used in the master are complex and long (INDEX, MATCH, and SUMPROD used together). They are long due to the path where the linked worksheets are stored. They are stored in the tenth folder from the root as below

='S:\aaa\bbb\ccc\ddd\eee\fff\ggg\hhh\iii\jjj\[Book2.xlsx]Sal'!$B$1

I am looking for a way to name the path 'S:\aaa\bbb\ccc\ddd\eee\fff\ggg\hhh\iii\jjj\[Book2.xlsx]Sal' as 'path' so that my formulas will look shorter, something like

=[path]Sal!$B$1

I have tried this with the Name Manager, but my values are not updating if for example I change the source workbook from Book2 to Book3.
Any ideas ?

 

[mole999]

you could map the root folder to a new ID i.e Z:\

 

[Mcstefan]

Thanks for the answer. I thought of that, but unfortunatelly it is not an option for me. Any other ideas?