Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Hi John and thank for your reply,

The solution for the 1st issue doesn't work, perhaps I didn't explain
my problem correct, let me try again:

I got 2 cells which both have validation, In 1st cell I choose values
from a List (Name Range), In 2nd cell I choose values from a list bur,
I use the INDIRECT(A1) function as a source to fill my list depending
on what I chose in 1st cell. To do this I created 3 Name Ranges: Basic
List, Specific1, Specific2

Basic List: Specific, Specific1, Specific2
Specific1: AAA, BBB
Specific2: CCC, DDD

Now with the INDIRECT in 2nd cell what I get, when choose Specific1
from the 1st the 2nd fills with AAA and BBB if I choose Specific2 for
the 1st the 2nd fills with CCC and DDD values.

But In 2nd cell I wrote an IF statement to accomplish a suggested value
for it depending the value in 1st cell: =IF(A1="Specific1";"AAA";"").
Don't if I can do it with a better way this one, please enlighten me
if...

So If the 1st cell has the value of Specific1 then there is no problem,
but when I choose something else I "block my self" through my
validation although I marked to ignore blanks. As you said this "" is
not equal to blank, what can I do...?

Did I miss something on your suggestion?

Thank you very much for your response.

John Coleman wrote:
Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Orthography (Corrections)

I choose values from a list bur - I choose values from a list but
Don't if I can do it with a better way this one - Don't know if I can
do it with a better way this one


x-rays wrote:
Hi John and thank for your reply,

The solution for the 1st issue doesn't work, perhaps I didn't explain
my problem correct, let me try again:

I got 2 cells which both have validation, In 1st cell I choose values
from a List (Name Range), In 2nd cell I choose values from a list bur,
I use the INDIRECT(A1) function as a source to fill my list depending
on what I chose in 1st cell. To do this I created 3 Name Ranges: Basic
List, Specific1, Specific2

Basic List: Specific, Specific1, Specific2
Specific1: AAA, BBB
Specific2: CCC, DDD

Now with the INDIRECT in 2nd cell what I get, when choose Specific1
from the 1st the 2nd fills with AAA and BBB if I choose Specific2 for
the 1st the 2nd fills with CCC and DDD values.

But In 2nd cell I wrote an IF statement to accomplish a suggested value
for it depending the value in 1st cell: =IF(A1="Specific1";"AAA";"").
Don't if I can do it with a better way this one, please enlighten me
if...

So If the 1st cell has the value of Specific1 then there is no problem,
but when I choose something else I "block my self" through my
validation although I marked to ignore blanks. As you said this "" is
not equal to blank, what can I do...?

Did I miss something on your suggestion?

Thank you very much for your response.

John Coleman wrote:
Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Hi,

Maybe the validation formula could involve lookup functions - keep the
Or with A2 = "" still one of the clauses, but the other clause could
use a combination of lookup fuctions and information functions:
something like

=Or(Not(IsErr(Match,A2,Indirect(A1),0)),A2 = "")

for the validation formula.

You could also consider *forcing* the user to select from the list by
locking the cell and letting an event-handler attached to the list
populate the cell.

Just a couple of ideas

-John Coleman

x-rays wrote:
Hi John and thank for your reply,

The solution for the 1st issue doesn't work, perhaps I didn't explain
my problem correct, let me try again:

I got 2 cells which both have validation, In 1st cell I choose values
from a List (Name Range), In 2nd cell I choose values from a list bur,
I use the INDIRECT(A1) function as a source to fill my list depending
on what I chose in 1st cell. To do this I created 3 Name Ranges: Basic
List, Specific1, Specific2

Basic List: Specific, Specific1, Specific2
Specific1: AAA, BBB
Specific2: CCC, DDD

Now with the INDIRECT in 2nd cell what I get, when choose Specific1
from the 1st the 2nd fills with AAA and BBB if I choose Specific2 for
the 1st the 2nd fills with CCC and DDD values.

But In 2nd cell I wrote an IF statement to accomplish a suggested value
for it depending the value in 1st cell: =IF(A1="Specific1";"AAA";"").
Don't if I can do it with a better way this one, please enlighten me
if...

So If the 1st cell has the value of Specific1 then there is no problem,
but when I choose something else I "block my self" through my
validation although I marked to ignore blanks. As you said this "" is
not equal to blank, what can I do...?

Did I miss something on your suggestion?

Thank you very much for your response.

John Coleman wrote:
Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Another typo:

=Or(Not(IsErr(Match(A2,Indirect(A1),0)),A2 = "")

John Coleman wrote:
Hi,

Maybe the validation formula could involve lookup functions - keep the
Or with A2 = "" still one of the clauses, but the other clause could
use a combination of lookup fuctions and information functions:
something like

=Or(Not(IsErr(Match,A2,Indirect(A1),0)),A2 = "")

for the validation formula.

You could also consider *forcing* the user to select from the list by
locking the cell and letting an event-handler attached to the list
populate the cell.

Just a couple of ideas

-John Coleman

x-rays wrote:
Hi John and thank for your reply,

The solution for the 1st issue doesn't work, perhaps I didn't explain
my problem correct, let me try again:

I got 2 cells which both have validation, In 1st cell I choose values
from a List (Name Range), In 2nd cell I choose values from a list bur,
I use the INDIRECT(A1) function as a source to fill my list depending
on what I chose in 1st cell. To do this I created 3 Name Ranges: Basic
List, Specific1, Specific2

Basic List: Specific, Specific1, Specific2
Specific1: AAA, BBB
Specific2: CCC, DDD

Now with the INDIRECT in 2nd cell what I get, when choose Specific1
from the 1st the 2nd fills with AAA and BBB if I choose Specific2 for
the 1st the 2nd fills with CCC and DDD values.

But In 2nd cell I wrote an IF statement to accomplish a suggested value
for it depending the value in 1st cell: =IF(A1="Specific1";"AAA";"").
Don't if I can do it with a better way this one, please enlighten me
if...

So If the 1st cell has the value of Specific1 then there is no problem,
but when I choose something else I "block my self" through my
validation although I marked to ignore blanks. As you said this "" is
not equal to blank, what can I do...?

Did I miss something on your suggestion?

Thank you very much for your response.

John Coleman wrote:
Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Hello John,

These suggestions (typos) occur circular references, any idea of what I
can I do about that?

Can I do this with VBA Code, I mean to select a value from my second
list depending the value from the 1st, cause so far only the output
Variable Target I see in worksheet events. My problem is how to refer
to the 2nd cell (List) when the value from the 1st has been selected.

Thanks in advance!

John Coleman wrote:
Another typo:

=Or(Not(IsErr(Match(A2,Indirect(A1),0)),A2 = "")

John Coleman wrote:
Hi,

Maybe the validation formula could involve lookup functions - keep the
Or with A2 = "" still one of the clauses, but the other clause could
use a combination of lookup fuctions and information functions:
something like

=Or(Not(IsErr(Match,A2,Indirect(A1),0)),A2 = "")

for the validation formula.

You could also consider *forcing* the user to select from the list by
locking the cell and letting an event-handler attached to the list
populate the cell.

Just a couple of ideas

-John Coleman

x-rays wrote:
Hi John and thank for your reply,

The solution for the 1st issue doesn't work, perhaps I didn't explain
my problem correct, let me try again:

I got 2 cells which both have validation, In 1st cell I choose values
from a List (Name Range), In 2nd cell I choose values from a list bur,
I use the INDIRECT(A1) function as a source to fill my list depending
on what I chose in 1st cell. To do this I created 3 Name Ranges: Basic
List, Specific1, Specific2

Basic List: Specific, Specific1, Specific2
Specific1: AAA, BBB
Specific2: CCC, DDD

Now with the INDIRECT in 2nd cell what I get, when choose Specific1
from the 1st the 2nd fills with AAA and BBB if I choose Specific2 for
the 1st the 2nd fills with CCC and DDD values.

But In 2nd cell I wrote an IF statement to accomplish a suggested value
for it depending the value in 1st cell: =IF(A1="Specific1";"AAA";"").
Don't if I can do it with a better way this one, please enlighten me
if...

So If the 1st cell has the value of Specific1 then there is no problem,
but when I choose something else I "block my self" through my
validation although I marked to ignore blanks. As you said this "" is
not equal to blank, what can I do...?

Did I miss something on your suggestion?

Thank you very much for your response.

John Coleman wrote:
Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Hi,

I see what you are doing now. I had originally thought that when you
were talking about lists then you were talking about a list-box
control. I gather now that you are talking about the list option in the
data validation (in particular, that in A2 you have the validation
"allow list" with "source: = indirect(A1)"). Is that correct?

Well, if so - we can't change the validation in A2 so that it combines
both a list and a formula. What you *can* do - if you want to be able
to put "=If(A1 = "specific1", "AAA","") into A2 without A2's validation
balking in the case that A1 < "specific1" is to extend the list
"specific1" by 1 more entry, namely ="" (equals the empty string). Be
sure to change the range refered to by specific 1 to incorporate it.
This just shows up as a scarcely noticable blank line on the bottom of
the validation list. A crude hack - but it does seem to work. If it
doesn't suit your purposes then you would need to go the VBA rout - use
an event-handler to capture when A1 changes its value and update the
contents of A2 accordingly.

Hopefuly this (finally) helps.

-John Coleman

x-rays wrote:
Hello John,

These suggestions (typos) occur circular references, any idea of what I
can I do about that?

Can I do this with VBA Code, I mean to select a value from my second
list depending the value from the 1st, cause so far only the output
Variable Target I see in worksheet events. My problem is how to refer
to the 2nd cell (List) when the value from the 1st has been selected.

Thanks in advance!

John Coleman wrote:
Another typo:

=Or(Not(IsErr(Match(A2,Indirect(A1),0)),A2 = "")

John Coleman wrote:
Hi,

Maybe the validation formula could involve lookup functions - keep the
Or with A2 = "" still one of the clauses, but the other clause could
use a combination of lookup fuctions and information functions:
something like

=Or(Not(IsErr(Match,A2,Indirect(A1),0)),A2 = "")

for the validation formula.

You could also consider *forcing* the user to select from the list by
locking the cell and letting an event-handler attached to the list
populate the cell.

Just a couple of ideas

-John Coleman

x-rays wrote:
Hi John and thank for your reply,

The solution for the 1st issue doesn't work, perhaps I didn't explain
my problem correct, let me try again:

I got 2 cells which both have validation, In 1st cell I choose values
from a List (Name Range), In 2nd cell I choose values from a list bur,
I use the INDIRECT(A1) function as a source to fill my list depending
on what I chose in 1st cell. To do this I created 3 Name Ranges: Basic
List, Specific1, Specific2

Basic List: Specific, Specific1, Specific2
Specific1: AAA, BBB
Specific2: CCC, DDD

Now with the INDIRECT in 2nd cell what I get, when choose Specific1
from the 1st the 2nd fills with AAA and BBB if I choose Specific2 for
the 1st the 2nd fills with CCC and DDD values.

But In 2nd cell I wrote an IF statement to accomplish a suggested value
for it depending the value in 1st cell: =IF(A1="Specific1";"AAA";"").
Don't if I can do it with a better way this one, please enlighten me
if...

So If the 1st cell has the value of Specific1 then there is no problem,
but when I choose something else I "block my self" through my
validation although I marked to ignore blanks. As you said this "" is
not equal to blank, what can I do...?

Did I miss something on your suggestion?

Thank you very much for your response.

John Coleman wrote:
Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Hi John,

Thank for your time and response,

Yes you understand the situation fully now, I did it with VBA and now I
can rest (for 3 secs).

Thank you very much!

John Coleman wrote:
Hi,

I see what you are doing now. I had originally thought that when you
were talking about lists then you were talking about a list-box
control. I gather now that you are talking about the list option in the
data validation (in particular, that in A2 you have the validation
"allow list" with "source: = indirect(A1)"). Is that correct?

Well, if so - we can't change the validation in A2 so that it combines
both a list and a formula. What you *can* do - if you want to be able
to put "=If(A1 = "specific1", "AAA","") into A2 without A2's validation
balking in the case that A1 < "specific1" is to extend the list
"specific1" by 1 more entry, namely ="" (equals the empty string). Be
sure to change the range refered to by specific 1 to incorporate it.
This just shows up as a scarcely noticable blank line on the bottom of
the validation list. A crude hack - but it does seem to work. If it
doesn't suit your purposes then you would need to go the VBA rout - use
an event-handler to capture when A1 changes its value and update the
contents of A2 accordingly.

Hopefuly this (finally) helps.

-John Coleman

x-rays wrote:
Hello John,

These suggestions (typos) occur circular references, any idea of what I
can I do about that?

Can I do this with VBA Code, I mean to select a value from my second
list depending the value from the 1st, cause so far only the output
Variable Target I see in worksheet events. My problem is how to refer
to the 2nd cell (List) when the value from the 1st has been selected.

Thanks in advance!

John Coleman wrote:
Another typo:

=Or(Not(IsErr(Match(A2,Indirect(A1),0)),A2 = "")

John Coleman wrote:
Hi,

Maybe the validation formula could involve lookup functions - keep the
Or with A2 = "" still one of the clauses, but the other clause could
use a combination of lookup fuctions and information functions:
something like

=Or(Not(IsErr(Match,A2,Indirect(A1),0)),A2 = "")

for the validation formula.

You could also consider *forcing* the user to select from the list by
locking the cell and letting an event-handler attached to the list
populate the cell.

Just a couple of ideas

-John Coleman

x-rays wrote:
Hi John and thank for your reply,

The solution for the 1st issue doesn't work, perhaps I didn't explain
my problem correct, let me try again:

I got 2 cells which both have validation, In 1st cell I choose values
from a List (Name Range), In 2nd cell I choose values from a list bur,
I use the INDIRECT(A1) function as a source to fill my list depending
on what I chose in 1st cell. To do this I created 3 Name Ranges: Basic
List, Specific1, Specific2

Basic List: Specific, Specific1, Specific2
Specific1: AAA, BBB
Specific2: CCC, DDD

Now with the INDIRECT in 2nd cell what I get, when choose Specific1
from the 1st the 2nd fills with AAA and BBB if I choose Specific2 for
the 1st the 2nd fills with CCC and DDD values.

But In 2nd cell I wrote an IF statement to accomplish a suggested value
for it depending the value in 1st cell: =IF(A1="Specific1";"AAA";"").
Don't if I can do it with a better way this one, please enlighten me
if...

So If the 1st cell has the value of Specific1 then there is no problem,
but when I choose something else I "block my self" through my
validation although I marked to ignore blanks. As you said this "" is
not equal to blank, what can I do...?

Did I miss something on your suggestion?

Thank you very much for your response.

John Coleman wrote:
Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance