Post the actual formula for WS_Name so I can do some testing.

Biff

"anny" wrote in message
...
Hello

Biff - I couldn't get this to work.

You have to refer to the cell that is being validated:
=C1=MID(WS_Name,2,1)

Also, I'm not sure that the cell needs to be refered to as you've
indicated. When I simply use =1 as the validation criteria in column
C, it works fine. Also, the named function works properly. When I write
=VALUE(MID(WS_Name,2,1) in any cell, I get the desired number. I just
can't get this expression to work as a validation criteria.

Any ideas out there?
Anny


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