Refer to an excel named range on another sheet using indirect

Question

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 ?

Solution 1

Further experimentation triggered by the very helpful tpkaplan answer allowed me to better locate the issue. Here are my findings.

The exact case I was describing in the question indeed works most of the time quite right (I had it fail, but never managed to understand why).

However, the case I thought I was testing doesn't.

In fact, you need to be very specific about what the so-called "Named range" refers to. There can be four different items that are handled differently by Excel:

• The name is a reference to a cell
• The name is a reference to a constant
• The name is a reference to a table of cells
• The name is a reference to any of the above mentioned categories, but through a formula.

Given these four categories, there is 3 ways to obtain a #REFerror:

• Wrong sheet name (as mentioned by tpkaplan) ;
• Range name does not exist (obviously) ;
• The name refers to anything else than a cell or a range of cells (my actual case was a formula).

Also note that if the name refers to a cell given using relative positioning (for example, the name refers to ='Sheet1'!$A5, so that the line changes depending on the cell you call the name from), when called from an other sheet, it will behave as if it was called from the cell A1 of the referred sheet.

So the way to go in order to get around the issue would be to have the name display the value it produces somewhere and to have another name that refers to that specific cell now containing the information you are trying to access. Then refer to that second name.

Solution 2

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.

Comments

[Samuel Albert]

It seems there are in fact more than one way to obtain this #REF error. As a matter of fact most of the useful cases will return this error. I develop this more in my answer. Thanks anyway for enabling me to get the grip on what was going on there.