Using the same Named Range in multiple worksheets, but with different values, in Excel

Use this trick to create use the same Named Range across multiple worksheets, but with each worksheet holding a different value

Named Ranges are one of Excel’s most useful features, and one that every Excel User should familiarise themselves with.

The default behaviour is for a named range to be relevant to the entire workbook. This is referred to as it’s ‘Scope’

Therefore, if you create a Named Range of ‘REGION’ in Sheet1, you can reference this value in Sheet2

This is the standard behaviour, and is what we usually want to happen.

But, what if you want to have a named range of REGION in each tab, with a different region value held in it?

For example, if you create a tab for each region in your business, you want the formulas in that sheet to refer to the relevant region. Rather than using a different named range (and therefore different formulas) for each region/tab, it’s possible to set a different instance of the same named range for each worksheet.

It’s all down to the Scope of the Named Range. Rather than setting at Workbook level, we can set the scope to be assigned to a specific worksheet.

To do this, you have to create the named range via the Defined Name option on the tool-bar (or via the Name Manager). Creating your named range by entering it in the Name Box (to the left of the formula bar) will default it to Workbook Scope, and it can’t be changed.

Formulas tab à Define Name

In the dialog box, give your range a Name (eg REGION), and change the Scope to the sheet you’re working on.

Ensure ‘Refers To’ is the correct cell reference for where you want your named range value.

‘OK’

As it is, if I was to try and use this named range in a different worksheet, I would get a #NAME? error…

If I did want to use it in a different worksheet, I would have to qualify the named range with its sheet name first

Now that I have created my first named range, I’m free to create more named ranges with the same name for different worksheets, using the same approach as before.

Alternatively, if the aim is to create a standard worksheet for each region, you can complete creating the page for your first region, then just copy the worksheet.

(Holding down the [CTRL] key, drag the tab across as though moving the sheet order

)

This will automatically duplicate the named range for the new sheet.

If you now go into your Named Range Manager, you will see the same range repeated, but with different values, referring to different sheets.

Hope that comes in useful to you.

1 comment