Linking a local Excel workbook to a named range on a Sharepoint hosted excel file

Question

Hi Everyone,

I have a few different spreadsheets that have similar columns that have the same data validation settings. Instead of trying to keep them all in sync, I created a setup spreadsheet housed on Sharepoint with named ranges for each of columns. I then recreated the same named range in the working spreadsheet that pointed to the named range on the sharepoint File.

This works perfectly, if I keep the sharepoint setup excel file open. As soon as it closes the link fails until I open the setup sheet again and refresh. I turned off auto updating of the links but it still fails.

Is this possible to do?

Thanks

Answer 1

Hi,

Yes, it is possible to set up named ranges in Excel that refer to a separate setup spreadsheet housed on SharePoint. However, what you described, where the link fails once the setup sheet is closed, is expected because the named ranges rely on the connection to the setup sheet to retrieve the data validation settings.

When the setup sheet is closed, Excel loses the connection to the named ranges in that sheet, resulting in the link failure. Even with the auto-updating of links turned off, the named ranges will still depend on the open setup sheet for the data validation settings.

To maintain the functionality, here are a few things you can do-

1. Use Power Query (Get & Transform Data) to import the data validation settings from the setup spreadsheet into the working spreadsheets. Power Query allows you to establish a connection to the setup sheet and load the data into the working sheet.
2. You can use VBA macros to automate the process of updating the named ranges in the working spreadsheets when the setup sheet changes. The macro can be triggered manually or set up to run automatically when specific events occur, such as opening the working spreadsheet. The macro would update the named ranges based on the data in the setup sheet, ensuring the data validation settings are up to date.

Best Regards.