Hi Biff

Here's the named formula WS_Name you requested

=MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,32)

It properly identifies the name of the worksheet when placed in a cell.

Thanks for your efforts
Anny


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