"#NAME?" Issue Shows In Formula From "Name Range" That Is Linked To A Different Excel File?

[olimits7]

Hi,

I setup different range names that point to a different Excel file and used these different name ranges in formulas that I have in my Excel file. Each time I open this Excel file, I get the following pop-up message which I click the "Update" button to but then the "#NAME?" issue shows.

This workbook contains links to one or more external sources that could be unsafe.
If you trust the links, update them to get the latest data. Otherwise, you can keep working with the data you have.

The only way I can get rid of the "#NAME?" issue from showing is opening up the other Excel file where my name ranges are pointing to.

Does anyone know how to get this to work without needing to open up my other Excel file each time?

This use to work fine with a previous name range I had but now that I added new name ranges that point to the same file I keep on getting this issue now.

Thank you!

 

[olimits7]

I can have 3 different "name ranges" pointing to the same sheet in a different Excel file, right? And use these 3 different "name ranges" in the same formula, right?

I don't think this would cause this "#NAME?" issue.

Thank you!

 

[thisoldman]

Does the defined name include the full path to the closed workbook?

https://support.office.com/en-us/ar...803-dd75-4668-ac6a-d7cca2a9b95f#bmdefine_name

 

[olimits7]

Yes, I setup the following 4 name ranges and this is what keeps on giving me the "#NAME?" issue.

pi ='R:\2018\[PI.xlsm]Pivot'!$A$5:$M$20

pi_new_dept ='R:\2018\[PI.xlsm]Pivot'!$A$28:$M$60

pi_intl ='R:\2018\[PI.xlsm]Pivot'!$A$68:$M$72

pi_all_countries ='R:\2018\[PI.xlsm]Pivot'!$A$80:$M$84

 

[thisoldman]

I don't know if the trusted locations settings would have any effect, but you could try them. File >> Options >> Trust Center >> Trust Center Settings.

Under Trusted Locations, try adding the folder to the User Locations.
Under Trusted Documents, if it's unchecked, try checking the box next to "Allow documents on a network to be trusted."

I'm now out of ideas.

 

[olimits7]

Ok, thank you...I'll give this a try.