Range-name Behaviour

[Peter]

I think I may have lost the plot.

If anyone can spare a couple of minutes to expain this to me I would be very
grateful.

Suppose I define a 3 cell range-name, say John, to refer to $A$2:SA$4
(definitely absolute references). If I enter =John in C1 it returns a value
error as I would expect. Obviously, you cannot fit the contents of 3 cells
into only one cell. What I do not understand is that if I now copy/paste or
autofill C1 down the column to say C5 I get the expected value errors in rows
1 and 5 but in rows 2,3&4 i.e. the same rows as used in the definition of
John I get the value of cells A2,A3&A4 respectively. Why don’t I get value
errors in all the cells through C1 to C5?

I bet I am missing something really obvious...

Many thanks in advance, Peter

 

[Niek Otten]

Hi Peter,

This behavior is called implicit intersection.
If a formula refers to a named range and only a one-cell operand is possible for the function, than it uses the intersection of
the formula and the named range (either row or column).
Note that this does not work for functions that can accept multi-cell operands (like MAX, AVERAGE, etc).
It also doesn't work (not anymore, it used to work in Excel 5) for arguments for UDFs; then you need explicit intersection, like
=MyUDF(Rangename 2:2) or force a one-cell reference through implicit intersection with another function, like =MyUDF(Rangename +
0).

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I think I may have lost the plot.
|
| If anyone can spare a couple of minutes to expain this to me I would be very
| grateful.
|
| Suppose I define a 3 cell range-name, say John, to refer to $A$2:SA$4
| (definitely absolute references). If I enter =John in C1 it returns a value
| error as I would expect. Obviously, you cannot fit the contents of 3 cells
| into only one cell. What I do not understand is that if I now copy/paste or
| autofill C1 down the column to say C5 I get the expected value errors in rows
| 1 and 5 but in rows 2,3&4 i.e. the same rows as used in the definition of
| John I get the value of cells A2,A3&A4 respectively. Why don't I get value
| errors in all the cells through C1 to C5?
|
| I bet I am missing something really obvious...
|
| Many thanks in advance, Peter
|
|

 

[Peter]

Niek,

Thank you very much. I even use intersection but I did not recognise it
here. I feel just a little embarrassed.

Peter.

 

[Niek Otten]

< I feel just a little embarrassed>

Don't! This behavior is known to very few users. If you use intersection, consider yourself an Excel expert.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Niek,
|
| Thank you very much. I even use intersection but I did not recognise it
| here. I feel just a little embarrassed.
|
| Peter.
|
| "Niek Otten" wrote:
|
| > Hi Peter,
| >
| > This behavior is called implicit intersection.
| > If a formula refers to a named range and only a one-cell operand is possible for the function, than it uses the intersection
of
| > the formula and the named range (either row or column).
| > Note that this does not work for functions that can accept multi-cell operands (like MAX, AVERAGE, etc).
| > It also doesn't work (not anymore, it used to work in Excel 5) for arguments for UDFs; then you need explicit intersection,
like
| > =MyUDF(Rangename 2:2) or force a one-cell reference through implicit intersection with another function, like =MyUDF(Rangename
+
| > 0).
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > |I think I may have lost the plot.
| > |
| > | If anyone can spare a couple of minutes to expain this to me I would be very
| > | grateful.
| > |
| > | Suppose I define a 3 cell range-name, say John, to refer to $A$2:SA$4
| > | (definitely absolute references). If I enter =John in C1 it returns a value
| > | error as I would expect. Obviously, you cannot fit the contents of 3 cells
| > | into only one cell. What I do not understand is that if I now copy/paste or
| > | autofill C1 down the column to say C5 I get the expected value errors in rows
| > | 1 and 5 but in rows 2,3&4 i.e. the same rows as used in the definition of
| > | John I get the value of cells A2,A3&A4 respectively. Why don't I get value
| > | errors in all the cells through C1 to C5?
| > |
| > | I bet I am missing something really obvious...
| > |
| > | Many thanks in advance, Peter
| > |
| > |
| >
| >
| >