View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Data Validation question

OK...........

I don't know why:

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

won't work. It works just fine when I test it in cells on the worksheet
itself but when applied as the formula in data validation it doesn't work. I
suspect it has something to do with the named formula.

This WILL work:

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

Or, put this formula in some out of the way cell like AA1:

=--MID(WS_name,2,1)

and then refer to that cell:

=C1=AA1

Side note:

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

do the exact same thing.

Biff

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