Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force entry into cell, based on validation selection in adjacent cell | Excel Worksheet Functions | |||
Force required entry in cell before leaving that cell | Excel Worksheet Functions | |||
How do I force entry in multiple cell ranges in one worksheet | Excel Worksheet Functions | |||
Force entry in one cell based on value of another cell | Excel Worksheet Functions | |||
Validation to force entry in a cell | Excel Worksheet Functions |