NewBie questions
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
|