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 dont 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