Named Range Name Keeps Changing On Its Own

[ezrizer]

Hello,

I have a master workbook ("database") containing many named ranges (ex "Contact"). I have another workbook template ("estimate") that I rename for each project and it too has named ranges (ex "Contact") referring to the named ranges in "database" (ex =DataBase.xlsm!Contact). I am saving "database" on multiple PC's and syncing to the cloud (skydrive / onedrive) so that I can access "database" from multiple PC's. What I'm finding is everything works fine as long as "estimate" or whatever I've renamed it is opened on the same PC it was created on. If I open estimate on another PC the named ranges in estimate are renamed by addition of a prefix including the complete path to "database" (ex "='C:\Users\Jason\SkyDrive\Documents\Daikin\ServiceTools\DataBase.xlsm'!Component"). This renaming prevents the named ranges in "estimate" from working on other pc's because this specific path doesn't exist on other PC's, only the PC it was created on. Is there a way to name the named ranges in estimate by only the workbook name (=DataBase.xlsm!Contact) without the entire path being automatically added as a prefix?

[:) Sixthsense :)]

Press Alt+E+K and click change source and locate the same workbook if that named range meant to refer that workbook

[ezrizer]

Thanks for your reply but I don't know what you're suggesting. I was able to partially solve my problem by eliminating the named ranges and doing something different for them however I also have Vlookup referencing the database workbook and the vlookup path is problematic in much the same way. When I set Vlookup in estimate to reference "database.xlsm" (both on the same PC) everything works fine. However If I move estimate to another PC also containing a file named "database.xlsm" it can't find it because it is not only looking for the workbook name but the entire file path which is where the problem lies. Although I have a workbook named "database.xlsm" located and open in another PC the file paths are different and Vlookup seems to wants the entire file path exactly as it was in the original PC. Is there a way for vlookup to ignore the file path and simply look for the file name?

[:) Sixthsense :)]

When you open the file in another system in which the "database.xlsm" will be in someother path, then refer my post #2 suggestion and locate the actual file by clicking the change source.

[ezrizer]

Thanks again but I don;t think that's not going to work. "estimate.xlsm" is a template that will be created and renamed in various PC's and moved about between various PC's many times. I need the template to be able to find "database.xlsm" without having to locate the exact filepath each time estimate is recreated.

[:) Sixthsense :)]

What about keeping the "estimate.xlsm" template file in open while opening the formula file?