Force entry in one cell due to the value in the other

[WT]

I have a situation that I hope you can help me with or at least point me in
the right direction.
I have a spread sheet where column "A" has a defind set of values
{A,B,C,D,E} and that cell has a validation set so that only those value can
be entered. Based on the entry in column A, I need to force an entry in
Column B but allow the user to enter from another set of selections
depending on the value in column A.
example:
A1 = A B1= set(F,G,H,J)
A2 = C B2= set(K,L,M,N)
Also I would like to force the user to make the entry in column "B" before
they can proceed to the next row.
Any ideas???

 

[Chip Pearson]

Create a range of data that contains the list of valid choice for
column A and the valid values of B for each A. That is, something like

a 1 2 3 4
b 11 22 33 44
c 111 222 333 444

where a, b, and c are the valid values for column A and the number to
the right of each of those are the valid values for column B. So, for
example, if A1 = b, then the only valid values for B1 are 11, 22, 33
or 44. In validation custom formula, use something like

=NOT(ISERROR(MATCH(B1,OFFSET($E$1,MATCH(A1,$E$1:$E$4,0)-1,1,1,4),0)))

Here, change $E$1:$E$4 to the column in the above table that contains
the valid values for A1 (e.g, the cells containing the a, b, and c
values), and change the 4 at the end of the OFFSET function to the
number of values that are allowed (e.g., 4 => 11, 22, 33, 44 valid
values).

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Marcelo]

create a list with the data you are looking for on b1, call this list "A"
(Formulas name manager);

on b1 (data - data validation) use list and on the source use =indirect(a1)

hth

--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"WT" escreveu:

 

[Marcelo]

Master,

is not easier to use a named range?
on the validation custom formula, I am just want to understand thanks
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Chip Pearson" escreveu:

 

[WT]

Much thanks to both of you for your help I can now start to replant my hair,
I will use both suggestions in different places and this does solve at least
half the issue.

The other half if either of you would like to make a suggestion, is once the
value in A1 is entered, I want to force the user to make an entry in B1 and
not let out until a selection is made. Also if possible I need to make it
conditional. Not every value entered in A1 requires an entry in B1.

Is this even possible or do I need to right a macro to do this??
If so, is there a macro that will automatically activate when the optimal
answers are entered into A1 so that a selection can be made in B1. Otherwise
I would prefer to move the active cell to the next column.