Hi,

A question about this function was followed up here, and KingMartin gave a very elegant solution. I have tried it, it works,
but I can not see why.

The second argument of COUNTIF is the criterion and it is set as the first cell of the range in Martin's solution. So
if I put a value in another cell of the range which duplicates something other then the first cell how does the validation
pick it up? And it does pick it up. Is the trick that the criterion cell is a relative refrence?

Hi Les,

In Martin's abscence...

Excel is kind of smart, eh? To see what our King has done, you want to follow his steps. Once completed, go to any individual cell and note how the cell reference has changed, by clicking Data->Validation...

E.g., A1 will check A1, A5 will check A5, etc... It has to do with the way Martin has set up his range reference via dollar signs.

Excel is very good at coercing well-attempted endeavours/constructs with Absolute vs. Relative cell referencing. E.g., Select A1 through A10, hit F2, and type in the following function:

=$B$1+B1

Now, while still active in the 'Function Bar' (Function Wizard?), hit Ctrl-Enter, at the exact same time. Note the result as you scroll down, cell by cell.

Clear as mud?

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 formula, =RC[-1].

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?

Martin

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

Thanks Guys!

I am usually working around the back of Excel and do not get to use the front end as much. Given the 300 plus
built-in functions I am probably duplicating heaps without even knowing. It is a real pleasure to see the KISS
principle in action.

Enjoy.