validation/formatting

B

BorisS

I have one cell which I will force to be either "internal" or "external" as a
value. I need to have two subsequent cells react based on that selection in
the following manner:

"internal" - cell 1 automatically is "internal" as well; cell 2 needs to
turn yellow (to prompt entry)
"external" - cell 1 needs to turn yellow and prompt entry of a company name;
cell 2 need to turn yellow once cell 1 is filled, prompting the second entry
into of a name of a person.

Any ideas of how to get this to work? Thx.
 
V

Vincnet.

Ideas could be using Validation lists and Conditional formats...
But without the file, it's rather difficult to explain.
Just some ideas:
For the Validation lists, use named list (such as Companies and People) and
a formula like: =IF($A$1="Internal","Internal",Companies) in cell 1, and a
formula like: =IF($A$1="Internal",Cell2,People) in cell2....
Does it help?
 
B

BorisS

so here's the challenge...

I want the referencing cell to end up being a list.
If it's "internal", it's a defined list of people. If it's not, it can be
freeform entry (challenge).

Here's the basic setup:

A1 B1 C1
Metro Internal
City External
City Internal

Assume I have two ranges named "Metro_Mech" and "City_Mech" and each has a
list of people under it that is basically the internal mechanic for that
region. Here's what I want the validation for C1 to do for me:

line 1: allow a dropdown that is limited to the list of metro_mech mechanics
line 2: allow freeform entry (because there are dozens of companies used for
repairs in this case for non-internal jobs
line 3: all a dropdown that is limited to the list of city_mech mechanics.

I have attempted to do the following in the "custom" value type for
vaildation (it did not seem to work in "list" mode):

if(b1="internal",indirect(a1&"_mech"),"").

I know the "" ending doesn't do anything, but I was at least trying to get
the referencing to work based on the "internal" selection, and then the look
to which range to go to. The second challenge is then how to allow freeform
text if it is not internal.

thoughts?
 
Top