2 Data Validations in a Cell

[John]

Is it possible to put 2 data Validations in a Cell?

I have posted previously on not allowing input in say, C5 unless there is a
value in A5 (i.e. A5=<>""), but if I also want whatever is input in C5 to
not exceed 20 characters, how would I do that in the same Validation

Thanks

 

[Biff]

Hi!

Try this:

=AND(A5<>"",LEN(C5)<=20)

Biff

 

[John]

Thanks Biff, unfortuately doesn't work

Hi!

Try this:

=AND(A5<>"",LEN(C5)<=20)

Biff

 

[Max]

=AND(A5<>"",LEN(C5)<=20)

Tried the above earlier, but couldn't get it to work in the DV for C5

Think it needs to be :
=AND(INDIRECT("A5")<>"",LEN(C5)<=20)

 

[Max]

Just posted a response ..
Try it for the DV in C5 as:
=AND(INDIRECT("A5")<>"",LEN(C5)<=20)

 

[John]

Superb Max. Thanks guys

1 Question, it is possible to input 21 numeric values, why is that (but not
alpha values)

 

[Max]

.. 1 Question, it is possible to input 21 numeric values,
why is that (but not alpha values)

Just tried it, we can even enter beyond 21 numeric char <g>
but Excel will auto-convert it to scientific format
(all digits after 15 dp will change to zeros)
and the DV won't trigger the "Re-try" prompt

If this is a problem, perhaps one simple work-around
is to pre-format the cell C5 as Text
(via: Format>Cells>Text)
albeit this'll affect downstream calcs (if any) pointing to C5

Hang around awhile for better insights from others ..

 

[Roger Govier]

Hi John

Developing on from the solution posted by Max, you can extend the AND()
function with another condition.
This will allow 20 characters of text, but only 15 numerics. The numerics
would be expressed as 1.xxxxxE+14.

=AND(A5<>"",IF(ISNUMBER(C5),LEN(TEXT(C5,"000000000000000"))<16,LEN(C5)<21))

Regards

Roger Govier

 

[John]

Thanks Guys these are great solutions. You just know that if you know what
you want, Excel can generally do it

 

[Dave Peterson]

I selected C5 and used this formula in data|validation:

=AND(A5<>"",LEN(C5)<=20)

But I unchecked the "ignore blank" option in the Settings tab of the
Data|Validation dialog.

And it seemed to work ok for me.

 

[Max]

.. But I unchecked the "ignore blank" option
in the Settings tab of the Data|Validation dialog.

Aha .. < slapping forehead furiously here >
that was the missed setting !
Thanks, Dave !

 

[Dave Peterson]

shhhh.

I saw it in a post from Debra Dalgleish recently. (I had missed it, too!)

Please keep it a secret <vbg>.

 

[Biff]

Please keep it a secret <vbg>.

You'll notice that *I* did!

Biff

 

[Debra Dalgleish]

You're welcome! <g>

shhhh.

I saw it in a post from Debra Dalgleish recently. (I had missed it, too!)

Please keep it a secret <vbg>.

 

[Max]

Please keep it a secret <vbg>.
Fortunately .. a lot of wonderful Excel secrets are in the open <g>

 

[Brisbane Rob]

I recently downloaded an add-in for Excel which I later removed vi
Control Panel. However, every time I fire up Excel I get an erro
message about being unable to load the add-in. Where do I go to get ri
of the auto-exec command?

Thank

 

[Nick Hodge]

Rob

Try Tools>Add-ins.... and uncheck it?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)


"Brisbane Rob" <[email protected]>
wrote in message
news:[email protected]...

 

[d1g_666]

Fortunately .. a lot of wonderful Excel secrets are in the open <g>

--

*Hello my name is Issam,
I just wanna ask u a question about the blank cells in exce
programme,
if you have a blank cell and its empty, is it right to add 'zero value
into it and why? * ** **

CHEERZ :confused

 

[Dave Peterson]

An empty cell is a strange beast.

It can be treated as text:
=if(a1="","it's empty","nope")
or it can be treated as a number
=if(a1=0,"it's zero","nope")

I'm not sure what you mean by add 'zero value' into it though.

 

[d1g_666]

Hi there,
' zero value ' is to type zero into the empty or not used cell

like for example,

A1 IS 31
A2 IS 46
A3 IS 19
A5 IS 77

here, A4 isnt used like u c
is it wrong if i added '0' number to A4 cell? and why?

cheerfully,
I$$AM