Hello Les & Nate,
thanks for jumping in Nate - nobody can give better explanation of why things work as they work then you
Here's how I see things, (at the risk of stating the obvious for you guys)
Behind the scenes, Excel doesn't work with the customer-friendly A1 notation, it rather works with the kind of obscure R1C1 notation.
=A1 becomes = RC
=$A$1 becomes =R1C1
=A1 when input into B1 becomes =R[-1]C[-1]
Akward, would you say? Yes, until you try to copy the formulae.
Enter =A1 into B1, copy, paste into some more cells in B. The formulae will say =A1, =A2, =A3 etc...
Now switch into R1C1 notation. What do you see? Yes, all the cells contain the same
Now switch back to A1, try with =$A$1, copy down, all the formulas will show the same text, =$A$1 (surprisingly
In R1C1, again you see the same formula in all cells, =R1C1.
On the same tact, if I enter this as validation into A1:A10:
=COUNTIF($A$1:$A$10,A1) = 1, which becomes actually =COUNTIF($A$1:$A$10,A2) = 1 in A2 etc...
what Excel really stores is an identic formula in all the cells in question:
=COUNTIF(R1C1:R10C1,RC) = 1
I don't know, does this make things clearer then mud?
P.S. As for the King... this has nothing to do with any Napoleonism of mine - King is simply a translation of my Czech surname