Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
WT WT is offline
external usenet poster
 
Posts: 16
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7,247
Default 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???

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,047
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,047
Default 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???

.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
WT WT is offline
external usenet poster
 
Posts: 16
Default 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???

.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Force entry into cell, based on validation selection in adjacent cell Richhall[_2_] Excel Worksheet Functions 3 June 18th 09 10:28 AM
Force required entry in cell before leaving that cell Retired Bill Excel Worksheet Functions 8 March 17th 09 11:57 PM
How do I force entry in multiple cell ranges in one worksheet CindyB Excel Worksheet Functions 0 July 10th 08 06:08 PM
Force entry in one cell based on value of another cell Kebbon Excel Worksheet Functions 3 August 16th 07 09:52 PM
Validation to force entry in a cell Matt D Francis Excel Worksheet Functions 4 October 4th 05 02:38 PM


All times are GMT +1. The time now is 05:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"