Work With Named Ranges on Different Sheets in Excel

As we know, each named range in excel is accessible in the whole book by default. But what if we want to have the same names on the different ranges? We can do so by setting the scope of the name to a worksheet.

Now you need to mention the worksheet each time you want to use a specific named range.
In this article, we will learn how to use Indirect to refer to names on different sheets.
Generic Formula


This is an array formula. But if you encapsulated it with a formula that accepts array you will not need to enter it with CTRL+SHIFT+Enter. Like


SheetName: It is the reference to the sheet name.
Name: The name of the named range.
Here, I have a file that has two sheets named “Customers” and “Products”. Both have named range as “Name”.
In a separate sheet, I have a drop-down with the names of these sheets. I want to get a count of Named range “Name” of the selected sheet.
Write this formula in B2:


You’ll get the count of selected sheet’s name range “Name”.

How does it work?

Well, this is quite easy.

If you right {=Products!Name} in any cell; it will return an array that will have all items in named range “Name” on sheet “Product”.

We do the same using the INDIRECT formula. This how formula translates if we have “Products” in A2.

=>COUNTA(INDIRECT(A2&"!Name")) then

=>COUNTA(INDIRECT("Products!Name")) then


And finally, we get our count.


So yeah guys, this is how you can use an indirect function with named ranges to refer to named ranges on different sheets, even if they have worksheet scope.

This tutorial was about how to use the INDIRECT function with named ranges in Excel 2016.  Let me know if you have any doubt about this tutorial or any other excel/VBA related topic. The comments section is all yours.

Related Articles:

Select From Drop Down And Pull Data From Different Sheet in Microsoft Excel 2016

All About Named Ranges In Excel

Dynamic Named Ranges in Excel

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.