Hi!

Well, I'm assuming that your named formula works properly.

You have to refer to the cell that is being validated:

=C1=MID(WS_Name,2,1)

Now, the MID function returns TEXT, so if:

=MID(WS_Name,2,1)


Returned 6, that 6 is TEXT and if the user entered a numeric 6 in cell C1 =
rejected!

So, try this:

=C1=--MID(WS_Name,2,1)

Or, format the target cell as TEXT (don't know if you really want to do
that, though!)

Biff

"anny" wrote in message
...
hi gurus

I have defined a Name for a function, WS_Name, that returns the name of
the active worksheet (eg A6-X, A4-R, B7-Q ...etc).

On each worksheet, I want to add validation to column C. The value in
colum C must be =MID(WS_Name,2,1). In the 3 sheets mentioned above,
column C should accept ONLY values of 6, 4 and 7 respectively.

I can't get the validation to work. I'm trying ...

DataValidationSettings Allow: Custom, Formula: =MID(WS_Name,2,1)
or

DataValidationSettings Allow: Custom, Formula:
=VALUE(MID(WS_Name,2,1))

No luck so far. Column C still takes any value. Any ideas?

TQ, Anny