scope of named ranges

[nils7]

Hi,

Is there any way for a named range to be accessible to all users of a network rather than just the worksheet?

My problem is: I have a formula that calculates the xth workingday in the UK taking into account public holidays, bank holidays etc. These dates are held in a named range.

My solution at the moment is to include a tab that defines this named range in each of the workbooks I use. Since I want to give other user access to using this formula I was wondering if there was a way to define the named range as having a network-wide scope? All users on the network have access to the same shared file structure, and could link into my original named range if that is somehow possible.

Thanks for your help,

Nils

 

[Jerry Sullivan]

Hi Nils,

I'm pretty sure there isn't a way to define a name that has network-wide scope in the way you describe, but there are many different approaches that you could take to achieve your objective.

Those could involve each user's workbook "reading" the values from the named range in workbook that all users can access through the network.

Does the named range that holds the dates refer to a static range address or is it a Dynamic Named Range(DNR)?
If it's a static reference, then you could just create a name in each of the users' workbooks that refers to that range in the external workbook.

If your range that holds the dates is a DNR, please provide a description of how the DNR is defined and I'll suggest alternate approaches that would work similarly.

 

[nils7]

Hi Jerry,

Thanks for taking the time to answer my question!

The workaround you are mentioning should do the job. How would i go about defining the range by referring to the external workbook then? The range is static.

Thanks

Nils

 

[Jerry Sullivan]

Here's one way.

Let's assume:
The workbook that has the static named range that all users need to reference is saved as:
"Z:\NetworkFolder\My Master.xlsx"
It has a static range named MyDates, which has Workbook scope and it Refers to: Sheet1!A2:A20

Open My Master.xlsx
Open one of the user workbooks and define a new Name, MyDates (it doesn't have to be the same as the Name in the external workbook, but it could be).
In the Refers to: box enter "=" (without quotes), then activate Master.xlsx and select the range Sheet1!A2:A20.

This should enter a link to the name into the Refers to box, in our example:
='My Master.xlsx'!MyDates

Now close My Master.xlsx and check the definition of MyDates in the user workbook.
It will have changed to ='Z:\NetworkFolder\My Master.xlsx'!MyDates

Now you can define MyDates in each user workbook using one of these two Refers to: references depending on whether the user has the external workbook open or closed.

 

[nils7]

Hi Jerry

Thanks for the detailed explanation. I will try it out straight away.

N