< 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

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