logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

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

May 3, 2016 0 comments Article Excel, Intermediate

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.

Related

Tags: Conditional, Excel, Names Ranges, Office, Productivity

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Using the IFS() and SWITCH() Functions to test for more than one condition in Excel 2016
Creating Virtual Tables in Power BI Using DAX
Showing Data Bars as a 'Proportion to Total' in Excel
Populating one Drop Down based on the Selection of Another in Excel – Without VBA
Format or Remove Borders from a Slicer or Timeline in Excel

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2022 - Theme by ThemeinProgress