Refer to an excel named range on another sheet using indirect

excel dynamic named-ranges
Samuel Albert picture Samuel Albert · Apr 1, 2015 · Viewed 12.1k times · Source

Let's say I have three sheets (let's say Sheet1, Sheet2 and Sheet3).

Sheet1 and Sheet2 contain each a sheet-level range named MyRange. In Sheet3, if I want to access MyRange from Sheet1, I will just be able to use ='Sheet1'!MyRange.

But now, I want Sheet3 To contain generic code and be able to refer either to Sheet1or Sheet2. So I would expect to be able to achieve the same thing using

=INDIRECT("'" & MySheetName & "'!MyRange")

However, I get the error #REF when I do that.

  • What do I do wrong ?
  • How can I work around that ?

Answer

tpkaplan picture tpkaplan · Apr 1, 2015

If MyRange consists of a multi-row, multi-column range, then make sure that you pressed ctrl-shift-enter when you entered =INDIRECT("'" & MySheetName & "'!MyRange"). If you forget to use ctrl-shift-enter, you will get a #VALUE error. However, you stated you have a #REF.

Using your code for a single-cell range, I get the result you are looking for. The only way I can get a #REF error is by using the wrong sheet name. I suspect that is your problem. Check the sheet name and repost with more detail if it still doesn't work.

Related questions

Referencing Dynamic Named Range in Excel Formula

I have a table in Excel with column headings that correspond to part of a dynamic named range elsewhere in my workbook. For example, I have these column headings: "10", "20", etc., and these dynamic named ranges: "ExampleRange10", "ExampleRange2", etc. I'd like …

Read More
Generate a filtered, dynamic drop down list

I need two dynamic drop down lists for data validation. One containing a unique list of continents to choose from, and then the second list which is a dynamically generated subset of countries based on the continent selected. The data …

Read More
Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)

I'm trying to create a chart with a range built dynamically using the INDIRECT function. Excel does recognize the range I am creating using INDIRECT as it highlights the corresponding range on the sheet: However when saving the chart, I …

Read More