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
Use CTRL+SHIFT+Enter.
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.
Example:
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
=>COUNTA(Products!Name)
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
Popular Articles:
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.
