Data Validation question

A

anny

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 ...

Data>Validation>Settings> Allow: Custom, Formula: =MID(WS_Name,2,1)
or

Data>Validation>Settings> Allow: Custom, Formula:
=VALUE(MID(WS_Name,2,1))

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

TQ, Anny
 
B

Biff

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
 
A

anny

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)
Click to expand...
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
 
A

anny

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
 
B

Biff

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 said:
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 said:
Post the actual formula for WS_Name so I can do some testing.

Biff
Click to expand...
Click to expand...
 
P

Peo Sjoblom

It's because if you use for instance indirect or offset that refers to
another cell (in this case I believe it is A1) you need to uncheck ignore
blanks in the validation window, I think =C1=--MID(WS_Name,2,1)
will work as validation if you do that.

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




Biff said:
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 said:
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 said:
Post the actual formula for WS_Name so I can do some testing.

Biff

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


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

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 ...

Data>Validation>Settings> Allow: Custom, Formula:
=MID(WS_Name,2,1) or

Data>Validation>Settings> Allow: Custom, Formula:
=VALUE(MID(WS_Name,2,1))

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

TQ, Anny
Click to expand...
Click to expand...
Click to expand...
 
B

Biff

Hi Peo!

Hmmm.....

Yes, unchecking Ignore blanks did allow the shorter formula to work.
However, the long formula:

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

Still contains Indirect calls and does work with Ignore blanks checked.

Biff

Peo Sjoblom said:
It's because if you use for instance indirect or offset that refers to
another cell (in this case I believe it is A1) you need to uncheck ignore
blanks in the validation window, I think =C1=--MID(WS_Name,2,1)
will work as validation if you do that.

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




Biff said:
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 said:
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


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

Biff

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


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

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 ...

Data>Validation>Settings> Allow: Custom, Formula:
=MID(WS_Name,2,1) or

Data>Validation>Settings> Allow: Custom, Formula:
=VALUE(MID(WS_Name,2,1))

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

TQ, Anny
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

custom data validation on cells with data validation values 2
Multiple data validation criteria? 1
Data Validation - Complex Q 2
About Data Validation 2
if condition with data validation list 5
Data validation for specific text, for example "blue"? 1
Data Validation Error 7
Vlookup and Data Validation Q 2

Top