Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
My scenario is below. I have three columns of dependent validation.
1. Select a value from dropdown list in A1. €“ O.K. 2. Dropdown list in B1 becomes dependent on selected value in A1. €“ O.K. 3. Select a value from list in B1. €“ O.K. 4. When there is a value in B1, A1 list is reduced to the single value which was selected in step 1. €“ O.K. 5. Dropdown list in C1 becomes dependent on selected value in B1. €“ O.K. 6. Select a value from list in C1. €“ O.K. 7. When there is a value in C1, I need the same to occur here as in step 4, B1 list is reduced to the single value which was selected in step 3. The validation code for the dropdown cells is: A1 - =IF(B1="",AList,INDEX(AColumn,MATCH(A1,AColumn,0)) ) B1- =OFFSET(AStart,MATCH(A1,AColumn,0)-1,1,COUNTIF(AColumn,A1),1) C1- =OFFSET(BStart,MATCH(B1,BColumn,0)-1,1,COUNTIF(BColumn,B1),1) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
There's a sample file here that may help you:
http://www.contextures.com/excelfiles.html Under Data Validation, look for 'Limit Data Validation Selection' jrrbama wrote: My scenario is below. I have three columns of dependent validation. 1. Select a value from dropdown list in A1. €“ O.K. 2. Dropdown list in B1 becomes dependent on selected value in A1. €“ O.K. 3. Select a value from list in B1. €“ O.K. 4. When there is a value in B1, A1 list is reduced to the single value which was selected in step 1. €“ O.K. 5. Dropdown list in C1 becomes dependent on selected value in B1. €“ O.K. 6. Select a value from list in C1. €“ O.K. 7. When there is a value in C1, I need the same to occur here as in step 4, B1 list is reduced to the single value which was selected in step 3. The validation code for the dropdown cells is: A1 - =IF(B1="",AList,INDEX(AColumn,MATCH(A1,AColumn,0)) ) B1- =OFFSET(AStart,MATCH(A1,AColumn,0)-1,1,COUNTIF(AColumn,A1),1) C1- =OFFSET(BStart,MATCH(B1,BColumn,0)-1,1,COUNTIF(BColumn,B1),1) -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Data Validation | Excel Worksheet Functions | |||
data validation lists | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |