Excel Reference Named Range in Another Sheet (3 Examples)

Excel’s named range is a powerful tool that allows users to refer to a specific range of cells by a name rather than a cell address. This feature makes it easier to manage large and complex spreadsheets. However, referencing named ranges in another worksheet can be tricky, especially for those are new to Excel. In this article, we will not only learn to create a named range but also explore how to Excel reference named range in another sheet. I hope this article will prompt you to start naming your range from now on and you can reference them in any worksheet.

What Is Named Range in Excel and How to Create It?

Names frequently refer to individuals, things, and locations in daily life. For instance, you would say “Washington, DC” rather than “the city lying at latitude 38.9072° N and longitude 77.0369° W.”

Similarly, you can refer to a single cell or a range of cells in Excel by name rather than by cell reference. Now you can just use the name you gave it rather than the cell reference (such as C4 or C4:C15). You can also use the name in formulas. Suppose you have named the range C6:C15 as “Jan_Sales_Target”. So, in a formula, instead of writing this:

You can write this:

The practice of naming ranges of cells comes in handy when you are dealing with large sets of data. This gives you a short approach to select a data range.

But how can you create a Named Range? There are a couple of ways you can create a Named Range. We will show you the most effective one.

Steps:

• First, select your range like we selected range B5:B15 and go to Formulas
• Next, select Define Name from Defined Names

• In the Name box, type your preferred name. We gave our range name “Employee” as the range lists the imaginary names of employees of a company.
• In the Scope box, the names of your worksheet can be seen. Remember, the named range will be applicable only to the worksheet if you choose specific worksheet. Since we want our range name to be available on every worksheet, we select “Workbook“.
• In the Refers to box, you will notice the range you already selected.
• Finally, press OK to apply the changes.

• You can see the name you typed in the Name Box beside the Formula

For every range of datasets you have selected, you can give them a unique name, which gives you an excellent opportunity to fast-track your work and save some time.

Read More: How to Create Dynamic Named Range in Excel

Excel Reference Named Range in Another Sheet: 3 Suitable Examples

In today’s article, we will discuss 3 simple methods for Excel reference Named Range in another sheet. But before going into the main part of the article, let’s familiarize ourselves with our dataset. We have 2 worksheets (Jan Sales and Feb Sales) that contain our data: Sales information (Sales Target & Sales Made) for a company for 2 months (January and February) and we will use the data from these sheets to reference in other worksheets.

We have named each column in each of our worksheets. Here’s what we have named, their cell references, and their scope in the following image. As you can see, we have fixed “Workbook” as the Scope for every named range except for the last two. The last two named ranges contain the same name, “Sales_Target” but in different worksheets. The difference between using Workbook as Scope instead of a certain worksheet is discussed in the first method.

We can create more worksheets and reference these data using their names instead of cell ranges. I hope this article will help you find what you are looking for.

Read More: How to Change Scope of Named Range in Excel

1. Excel Reference Named Range in Another Sheet: Default Referencing Technique

For our first method, we will use the most basic referencing technique available in Excel. We will demonstrate how you can reference a named range in another sheet using unique named ranges and similar named ranges scoped in different sheets.

1.1. Referencing the Range of Unique Names from Different Sheets

For this example, we have named range for range D5:D15 from both the Jan Sales and Feb Sales worksheets as Jan_Sales_Made and Feb_Sales_Made. Now, we will reference them in a new worksheet, “Reference NR(Default) Ex1.1”.

To reference a unique named range in another sheet, remember to use “Workbook” as Scope. This will make the named range available throughout the workbook.

• First, select the cell where you want to reference your named range. We have selected cell B5.
• Next, type the name of range, as you created. You don’t have to type the whole name. Excel will pick up the name from the first 2/3 letters.

• Then press Enter and you will see that sales data from Jan Sales worksheet has been imported into the new worksheet.
• In case you want to reference a named range in a formula like with the SUM function, just type the function first, then type the range name and press Enter.

1.2. Referencing the Range of Similar Names from Different Sheets

Suppose you have created named ranges on different sheets using the same name. Then how will you reference those ranges and get your desired data? Well, in that case, you’ll have to mention the worksheet name while referencing.

We have created two named ranges with the same name, “Sales_Target”. In the Jan Sales worksheet, the range D5:D15 is named as such, but only the Scope has been selected as Jan Sales instead of Workbook. For Feb Sales worksheet, the same named range’s Scope has been selected Feb Sales. As a result, the named ranges will only work on those particular worksheets. So when you reference them in a different worksheet, follow these steps.

• First, select the cell where you want to reference your named range. We have selected cell B5.
• Next, type the worksheet name where the named range exists. Remember to use “!” after the worksheet name. You must include the worksheet name in single quotation marks if it contains spaces or other non-alphabetical characters.

• Then press Enter and you will see that the sales target data from Jan Sales worksheet has been imported into the new worksheet.
• In case you want to reference a similar named range in a formula, just type the function first, then follow the mentioned method and press Enter.

2. Reference Named Range Using Keyboard Shortcut

In the previous method, you manually typed named ranges, which is a little bit tiresome. You can fast-track the process by using a keyboard shortcut.

Excel stores every named range in its memory. So, when you want to access all of them at once, you can press F3, which will prompt the Paste Name window. The Paste Name dialogue box lists every named range that is given “Workbook” as Scope. But if you want to see every named range irrespective of Scope, press Ctrl + F3 which will show Name Manager listing every named range.

Now, to reference a named range in another sheet using the keyboard shortcut, follow these steps.

• First, select the first cell where you want to reference your named range. We have selected cell B5.
• Next, press F3 to get to the Paste Name Here, select the named range you want to reference. We are selecting Jan_Sales_Target. Then, press OK and subsequently hit Enter.
• In case you want to reference a named range in a formula, just type the function first, then press F3 and select the named range. Press OK and Enter, respectively.

Read More: How to Paste Range Names in Excel

3. Use the INDIRECT Function to Reference Named Range

When it comes to referencing named ranges, the INDIRECT function provides great flexibility. The INDIRECT function takes a text string from a specified cell and uses it as a reference to return the value of the named range.

3.1. Referencing Different Named Ranges from Different Sheet Using INDIRECT Function

In this example, we will use the INDIRECT function to reference unique named ranges to another sheet. For this purpose, we listed our necessary named range in cells B5 and B6.

We will import the sales made by the company in January from Jan Sales worksheet. So our named range is Jan_Sales_Made  and we listed it as a text string in cell B5 in our new worksheet.

• First, select your output cell C5 and insert this formula.

• Then, press Enter and your data will be imported.
• You can also use INDIRECT function nested in another function. In our example, we used it in conjunction with the SUM function to get the sum total of Jan_Sales_Made.

3.2. Referencing the Range of Similar Names from Different Sheets Using INDIRECT Function

While referencing the similar named ranges located on different sheets, it is necessary to include the worksheet name with the named range. Let’s see how we can reference similar named ranges in another sheet using the INDIRECT function.

We will import the sales targets set by the company for January and February from Jan Sales and Feb Sales worksheets. So our named range is Sales_Target  for both sheets (Scope for this named range is Jan_Sales and Feb_Sales respectively), and we listed it as a text string in cell B5 in our new worksheet.

• To import the sales target from Jan Sales worksheet, first, select your output cell C5 and insert this formula.

• Then, press Enter and your data will be imported.
• Follow the same rule while nesting INDIRECT function into another function. In our example, we used it in conjunction with the SUM function to get the sum total of Sales_Target for January.

Note:

You don’t need to use single quotation marks if there are no spaces or non-alphabetical characters in the worksheet names. For example, If we named our worksheets like JanSales or just Sales, we could’ve simply referenced our named range in another sheet like this.

So, be careful with the naming cause it may result in #REF error if you don’t use the correct method while referencing.

Read More: Excel INDIRECT Function with Named Range

Several Practical Examples of Excel Reference Named Range in Another Sheet

We have discussed how you can reference named range in another worksheet in Excel. Let’s see some examples of referencing named ranges in another worksheet while working with other functions in Excel. We will show VLOOKUP function and a combination of INDEX-MATCH functions to reference.

Example 1: Employing VLOOKUP Function to Reference Named Range in Another Sheet

If you have named your entire table range, and you want to import data in another sheet from that named range matching certain values, you can reference the named range using the VLOOKUP function.

For this example, we have named range for range B5:D15 from both the Jan Sales and Feb Sales worksheets as Jan_Record and Feb_Record. Now, we will reference them in a new worksheet, “Ex.1 VLOOKUP”.

Now, in the new worksheet, “Ex.1 VLOOKUP”, in column B, we have the employees. We want to import the sales made by the employees for January and February in the new worksheet using the VLOOKUP function.

• In our named ranges (Jan_Record and Feb_Record), the 3rd column (D5:D15) contains the necessary data. So, in cell C5 of worksheet “1 VLOOKUP”, we write this formula.

• In a similar fashion to import data from Feb Sales worksheet, in cell D5, we write this formula.

The formula looks at the range B5:B15 and searches the cell contents in the named range (Jan_Record). Then returns the value of the 3rd column from the named range for every row. For an exact match return, we use the last argument as False.

Note:

When using VLOOKUP function, always remember the lookup_value must be in the leftmost column of your range. Otherwise, VLOOKUP shows an error.

Read More: How to Use Named Range in Excel VLOOKUP Function

Example 2: Combining the INDEX and MATCH Functions to Reference Named Range

Since the VLOOKUP function has limitations regarding the location of lookup_value, we can remedy that by using the combination of INDEX and MATCH functions.

For this example, we have named range for range D5:D15 from both the Jan Sales and Feb Sales worksheets as Jan_Sales_Made and Feb_Sales_Made. Now, we will reference them in a new worksheet, “Ex.2 INDEX-MATCH”. We also named B5:B15 as “Employee” which was demonstrated at the beginning of the article.

Now, in the new worksheet, “Ex.2 INDEX-MATCH”, in column B, we have the employees. We want to import the sales made by the employees in January and February in the new worksheet using the combination of INDEX-MATCH functions.

• In cell C5 of worksheet “2 INDEX-MATCH”, we write this formula.

• In a similar fashion to import data from Feb Sales worksheet, use the following formula on cell D5.

How to Reference Named Range from Another Open Workbook in Excel

Sometimes, you want to import data from another Excel file. In that case, you need to reference externally to import data. Depending on whether the source workbook is open or closed, there are two ways you can reference a named range externally.

If your source workbook is opened, then you need to mention the source workbook name followed by an exclamation point (!) in the formula. Now depending on the Scope, referencing a named range can get complicated.

If Scope is set to “Workbook” while naming range, then you can reference externally like this.

If Scope is set to a particular worksheet while naming range, then you can reference it externally like this.

For our example, we have the source workbook, “Workbook1.xlsx” where we have named range Jan_Record and Scope set to Workbook. We want to import Jan_Record to “Workbook2.xlsx”.

• Selecting cell B5, we give reference externally like this.

If we would’ve set Scope to Jan Sales worksheet, we would’ve referenced it like this.

How to Reference Named Range from a Closed Workbook in Excel

You can also reference named range externally when the source workbook is closed. You must include the location address for your external reference if the source workbook is closed.

If your source workbook is closed, then you need to mention the source workbook’s file path before the workbook name followed by an exclamation mark (!) in the formula. Now depending on the Scope, referencing named range can get complicated.

If Scope is set to “Workbook” while naming range, then you can reference externally like this

If Scope is set to a particular worksheet while naming range, then you can reference externally like this.

For our example, we have a closed source workbook, “Workbook1.xlsx” located within the Documents folder on drive C, We want to import Jan_Record to “Workbook2.xlsx”.

• Selecting cell B5, we reference externally like this.

Frequently Asked Questions

1. How do I update a named range reference in another worksheet when I move the range to a new location?

Ans: To update a named range reference in another worksheet when the range is moved to a new location, you need to redefine the named range.

• Go to the Formulas tab, click on Name Manager from Defined Names
• Select the named range, and click on Edit.
• Change the range reference to the new location and click OK.

2. What happens if I delete a named range that is referenced in another worksheet?

Ans: If you delete a named range that is referenced in another worksheet, any formulas or functions that use the named range will return a #REF! error. You need to update the formula or function with a valid range reference or redefine the named range to fix the error.

3. How do I protect a named range in another worksheet from being deleted or modified?

Ans: To protect a named range in another worksheet from being deleted or modified, you need to protect the worksheet.

• Go to the Review tab, and click on Protect Sheet.
• Select the options to allow users to Select locked cells and Edit objects. Then, lock the cells containing the named range.

Download Practice Workbook

Download this file to practice and better understand the topic.

Conclusion

In conclusion, understanding how to Excel reference named range in another sheet can greatly enhance your proficiency and streamline your data management processes. These three methods: manual referencing, Keyboard Shortcut, and INDIRECT functions can unleash the full potential of named ranges in Excel. Mastering them enhances productivity and efficiency, benefiting financial models, data analysis, and project management. Elevate your Excel skills by exploring these techniques for dynamic and organized spreadsheets. We hope that from now on you will apply these techniques, and elevate your Excel expertise to new heights.

Related Articles

• How to Create Dynamic Named Range in Excel
• How to Use Dynamic Named Range in an Excel Chart
• Define Names with the Create from Selection Tool in Excel
• How to Ignore Blank Cells in Named Range in Excel

<< Go Back to Named Range | Excel Formulas | Learn Excel