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

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???

--
Thank you

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

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




On Tue, 30 Mar 2010 10:58:02 -0700, WT
wrote:

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???

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

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:

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???

--
Thank you

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

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:

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




On Tue, 30 Mar 2010 10:58:02 -0700, WT
wrote:

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???
.

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

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.
--
Your most humble student......


"Marcelo" wrote:

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:

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




On Tue, 30 Mar 2010 10:58:02 -0700, WT
wrote:

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???
.