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


"Peter" wrote in message ...
|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
|
|