Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
Using Data Validation, I have created a dependent list (based on instructions
found on the Contextures web site: http://www.contextures.com/xlDataVal02.html). I want to create a conditional format whereby if someone changes the selection in the first data validation list without changing the corresponding selection in the dependent list (i.e., second list), the cell value in the dependent list will turn red. Can anyone tell me how to go about doing this? Thanks, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
First, thanks for visiting Debra Dalgleish's Contextures website for
instructions on Dependent Lists. Now, I have a lot less expaining to do. Since the dependent list is based on a Named List, try this: With A1 containing the parent list B1 containing the dependent list and the Named list: MyNamedList Then Select B1 <format<conditional formatting Formula is: =ISERROR(MATCH($B$1,MyNamedList,0)) Click the [format] button and set your format Click the [OK] buttons and you're done. Does that help? *********** Regards, Ron XL2002, WinXP "Bob" wrote: Using Data Validation, I have created a dependent list (based on instructions found on the Contextures web site: http://www.contextures.com/xlDataVal02.html). I want to create a conditional format whereby if someone changes the selection in the first data validation list without changing the corresponding selection in the dependent list (i.e., second list), the cell value in the dependent list will turn red. Can anyone tell me how to go about doing this? Thanks, Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
Ron,
Thank you for providing a solution to my problem. Unfortunately, I could not seem to get it to work. After some lengthly experimentation, I came up with my own solution: =ISNA(INDEX(INDIRECT(VLOOKUP($A2,PhaseLookup,2,0)) ,MATCH($B2,INDIRECT(VLOOKUP($A2,PhaseLookup,2,0)), 0),1)) whe Column A (starting in row 2) contains the in-cell dropdown box for the parent list Column B (starting in row 2) contains the in-cell dropdown box for the dependent list Please note that since I am "Using Items with Illegal Characters" (refer to Debra Dalgleish's Contextures website), I had to create a lookup table. Bob "Ron Coderre" wrote: First, thanks for visiting Debra Dalgleish's Contextures website for instructions on Dependent Lists. Now, I have a lot less expaining to do. Since the dependent list is based on a Named List, try this: With A1 containing the parent list B1 containing the dependent list and the Named list: MyNamedList Then Select B1 <format<conditional formatting Formula is: =ISERROR(MATCH($B$1,MyNamedList,0)) Click the [format] button and set your format Click the [OK] buttons and you're done. Does that help? *********** Regards, Ron XL2002, WinXP "Bob" wrote: Using Data Validation, I have created a dependent list (based on instructions found on the Contextures web site: http://www.contextures.com/xlDataVal02.html). I want to create a conditional format whereby if someone changes the selection in the first data validation list without changing the corresponding selection in the dependent list (i.e., second list), the cell value in the dependent list will turn red. Can anyone tell me how to go about doing this? Thanks, Bob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
Thanks for the feedback, Bob.....I'm glad you got that working.
*********** Regards, Ron XL2002, WinXP "Bob" wrote: Ron, Thank you for providing a solution to my problem. Unfortunately, I could not seem to get it to work. After some lengthly experimentation, I came up with my own solution: =ISNA(INDEX(INDIRECT(VLOOKUP($A2,PhaseLookup,2,0)) ,MATCH($B2,INDIRECT(VLOOKUP($A2,PhaseLookup,2,0)), 0),1)) whe Column A (starting in row 2) contains the in-cell dropdown box for the parent list Column B (starting in row 2) contains the in-cell dropdown box for the dependent list Please note that since I am "Using Items with Illegal Characters" (refer to Debra Dalgleish's Contextures website), I had to create a lookup table. Bob "Ron Coderre" wrote: First, thanks for visiting Debra Dalgleish's Contextures website for instructions on Dependent Lists. Now, I have a lot less expaining to do. Since the dependent list is based on a Named List, try this: With A1 containing the parent list B1 containing the dependent list and the Named list: MyNamedList Then Select B1 <format<conditional formatting Formula is: =ISERROR(MATCH($B$1,MyNamedList,0)) Click the [format] button and set your format Click the [OK] buttons and you're done. Does that help? *********** Regards, Ron XL2002, WinXP "Bob" wrote: Using Data Validation, I have created a dependent list (based on instructions found on the Contextures web site: http://www.contextures.com/xlDataVal02.html). I want to create a conditional format whereby if someone changes the selection in the first data validation list without changing the corresponding selection in the dependent list (i.e., second list), the cell value in the dependent list will turn red. Can anyone tell me how to go about doing this? Thanks, Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
I used Ron's formula below, with success, but I would like something little
different. I would like the B1 cell (dependent list) to be blank if the A1 cell is changed after the B1 cell has been completed. So, a user enters a value in A1, the list in B1 is restricted. They select something from B1. They they go back to A1 and either change the value or delete the value - at this point I would like B1 to go blank. Any ideas? THANKS |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
You'd need VBA code to effect what you want.
Try working with this and post back with any questions Assumptions: Parent List cells are in A1:A10 Dependent List cells are in B10 Copy the below VBA code into the sheet module of the worksheet with the Data Validation. You get there this way: Right-Click on the sheet tab Select "View Code" '----------Start of Code------------ Private Sub Worksheet_Change(ByVal Target As Range) Dim rngAllParentCells As Range Dim rngDepCells As Range Dim rngCell As Range Set rngAllParentCells = Range("A1:A10") Set rngDepCells = Intersect(Target, rngAllParentCells) If Not rngDepCells Is Nothing Then For Each rngCell In rngDepCells.Cells 'Move 1 cell to the right and clear contents rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents Next rngCell End If Set rngAllParentCells = Nothing Set rngDepCells = Nothing Set rngCell = Nothing End Sub '----------End of Code------------ Now....whenever one of the A1:A10 DV cells is changed, the corresponding B1:B10 cell is cleared Does that help? *********** Regards, Ron XL2002, WinXP "Shelly" wrote: I used Ron's formula below, with success, but I would like something little different. I would like the B1 cell (dependent list) to be blank if the A1 cell is changed after the B1 cell has been completed. So, a user enters a value in A1, the list in B1 is restricted. They select something from B1. They they go back to A1 and either change the value or delete the value - at this point I would like B1 to go blank. Any ideas? THANKS |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
PERFECT!!! Thanks!
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
"Ron Coderre" wrote: You'd need VBA code to effect what you want. Try working with this and post back with any questions Assumptions: Parent List cells are in A1:A10 Dependent List cells are in B10 Copy the below VBA code into the sheet module of the worksheet with the Data Validation. You get there this way: Right-Click on the sheet tab Select "View Code" '----------Start of Code------------ Private Sub Worksheet_Change(ByVal Target As Range) Dim rngAllParentCells As Range Dim rngDepCells As Range Dim rngCell As Range Set rngAllParentCells = Range("A1:A10") Set rngDepCells = Intersect(Target, rngAllParentCells) If Not rngDepCells Is Nothing Then For Each rngCell In rngDepCells.Cells 'Move 1 cell to the right and clear contents rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents Next rngCell End If Set rngAllParentCells = Nothing Set rngDepCells = Nothing Set rngCell = Nothing End Sub '----------End of Code------------ Now....whenever one of the A1:A10 DV cells is changed, the corresponding B1:B10 cell is cleared Does that help? *********** Regards, Ron XL2002, WinXP "Shelly" wrote: I used Ron's formula below, with success, but I would like something little different. I would like the B1 cell (dependent list) to be blank if the A1 cell is changed after the B1 cell has been completed. So, a user enters a value in A1, the list in B1 is restricted. They select something from B1. They they go back to A1 and either change the value or delete the value - at this point I would like B1 to go blank. Any ideas? THANKS |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
This worked for me as well. Thank you very much. Now I have a related
question My ParentList is in col C. Col D is dependent upon C. Col E is dependent on D. How can I modify the code so that when some deletes data from Col D or E, Col C Parent List goes blank? Thanks for your help. Toni "Ron Coderre" wrote: You'd need VBA code to effect what you want. Try working with this and post back with any questions Assumptions: Parent List cells are in A1:A10 Dependent List cells are in B10 Copy the below VBA code into the sheet module of the worksheet with the Data Validation. You get there this way: Right-Click on the sheet tab Select "View Code" '----------Start of Code------------ Private Sub Worksheet_Change(ByVal Target As Range) Dim rngAllParentCells As Range Dim rngDepCells As Range Dim rngCell As Range Set rngAllParentCells = Range("A1:A10") Set rngDepCells = Intersect(Target, rngAllParentCells) If Not rngDepCells Is Nothing Then For Each rngCell In rngDepCells.Cells 'Move 1 cell to the right and clear contents rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents Next rngCell End If Set rngAllParentCells = Nothing Set rngDepCells = Nothing Set rngCell = Nothing End Sub '----------End of Code------------ Now....whenever one of the A1:A10 DV cells is changed, the corresponding B1:B10 cell is cleared Does that help? *********** Regards, Ron XL2002, WinXP "Shelly" wrote: I used Ron's formula below, with success, but I would like something little different. I would like the B1 cell (dependent list) to be blank if the A1 cell is changed after the B1 cell has been completed. So, a user enters a value in A1, the list in B1 is restricted. They select something from B1. They they go back to A1 and either change the value or delete the value - at this point I would like B1 to go blank. Any ideas? THANKS |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
I want to make sure that what you're asking for is what you really need.
(I'll assume all of the DV lists are on Row_2) You indicated that a change to either D2 or E2 causes a change in C2, the parent list, but no other changes. Typically, in the scenario you described..... whe C2 is the parent DV list cell D2 is dependent on C2 E2 is dependent on D2 then: changing E2 has no impact on C2 and D2 changing D2 changes E2, but not C2 changing C2 changes both D2 and E2 Could you restate the request, with plenty of details, so can receive a better quality response? *********** Regards, Ron XL2002, WinXP "Toni" wrote: This worked for me as well. Thank you very much. Now I have a related question My ParentList is in col C. Col D is dependent upon C. Col E is dependent on D. How can I modify the code so that when some deletes data from Col D or E, Col C Parent List goes blank? Thanks for your help. Toni "Ron Coderre" wrote: You'd need VBA code to effect what you want. Try working with this and post back with any questions Assumptions: Parent List cells are in A1:A10 Dependent List cells are in B10 Copy the below VBA code into the sheet module of the worksheet with the Data Validation. You get there this way: Right-Click on the sheet tab Select "View Code" '----------Start of Code------------ Private Sub Worksheet_Change(ByVal Target As Range) Dim rngAllParentCells As Range Dim rngDepCells As Range Dim rngCell As Range Set rngAllParentCells = Range("A1:A10") Set rngDepCells = Intersect(Target, rngAllParentCells) If Not rngDepCells Is Nothing Then For Each rngCell In rngDepCells.Cells 'Move 1 cell to the right and clear contents rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents Next rngCell End If Set rngAllParentCells = Nothing Set rngDepCells = Nothing Set rngCell = Nothing End Sub '----------End of Code------------ Now....whenever one of the A1:A10 DV cells is changed, the corresponding B1:B10 cell is cleared Does that help? *********** Regards, Ron XL2002, WinXP "Shelly" wrote: I used Ron's formula below, with success, but I would like something little different. I would like the B1 cell (dependent list) to be blank if the A1 cell is changed after the B1 cell has been completed. So, a user enters a value in A1, the list in B1 is restricted. They select something from B1. They they go back to A1 and either change the value or delete the value - at this point I would like B1 to go blank. Any ideas? THANKS |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
Hi , I have used the Debra Dalgleish's Contextures website for
instructions on Dependent Lists, and it works excellent , but now I need to add items to my validation Lists , let me explain , if the user type a new element I need to add that new one to the validation list. How can I do it ? Thank you "Ron Coderre" wrote: First, thanks for visiting Debra Dalgleish's Contextures website for instructions on Dependent Lists. Now, I have a lot less expaining to do. Since the dependent list is based on a Named List, try this: With A1 containing the parent list B1 containing the dependent list and the Named list: MyNamedList Then Select B1 <format<conditional formatting Formula is: =ISERROR(MATCH($B$1,MyNamedList,0)) Click the [format] button and set your format Click the [OK] buttons and you're done. Does that help? *********** Regards, Ron XL2002, WinXP "Bob" wrote: Using Data Validation, I have created a dependent list (based on instructions found on the Contextures web site: http://www.contextures.com/xlDataVal02.html). I want to create a conditional format whereby if someone changes the selection in the first data validation list without changing the corresponding selection in the dependent list (i.e., second list), the cell value in the dependent list will turn red. Can anyone tell me how to go about doing this? Thanks, Bob |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
I'd like to help, but I'll be traveling with no internet access for the next
10 days. Since the original thread is so old.....try posting a new question so the excellent talent that frequents these groups can help you. (If you do that...leave a post here indicating that, so you don't have 2 separate groups of people helping) *********** Regards, Ron XL2003, WinXP "Angeles" wrote: Hi , I have used the Debra Dalgleish's Contextures website for instructions on Dependent Lists, and it works excellent , but now I need to add items to my validation Lists , let me explain , if the user type a new element I need to add that new one to the validation list. How can I do it ? Thank you "Ron Coderre" wrote: First, thanks for visiting Debra Dalgleish's Contextures website for instructions on Dependent Lists. Now, I have a lot less expaining to do. Since the dependent list is based on a Named List, try this: With A1 containing the parent list B1 containing the dependent list and the Named list: MyNamedList Then Select B1 <format<conditional formatting Formula is: =ISERROR(MATCH($B$1,MyNamedList,0)) Click the [format] button and set your format Click the [OK] buttons and you're done. Does that help? *********** Regards, Ron XL2002, WinXP "Bob" wrote: Using Data Validation, I have created a dependent list (based on instructions found on the Contextures web site: http://www.contextures.com/xlDataVal02.html). I want to create a conditional format whereby if someone changes the selection in the first data validation list without changing the corresponding selection in the dependent list (i.e., second list), the cell value in the dependent list will turn red. Can anyone tell me how to go about doing this? Thanks, Bob |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Dependent Lists & Conditional Formatting
Angeles
Debra Dalgleish has a sample workbook for adding items to a DV list. DV0012 - Update Validation List -- type a new value in a cell that contains data validation, and it's automatically added to the source list, and the list is sorted; a macro automates the list updates. DataValListAddSort.zip 11 kb http://www.contextures.on.ca/excelfiles.html#DataVal Scroll down until you find DV0012 to download the file. Gord Dibben MS Excel MVP On Wed, 3 Oct 2007 19:05:01 -0700, Ron Coderre wrote: I'd like to help, but I'll be traveling with no internet access for the next 10 days. Since the original thread is so old.....try posting a new question so the excellent talent that frequents these groups can help you. (If you do that...leave a post here indicating that, so you don't have 2 separate groups of people helping) *********** Regards, Ron XL2003, WinXP "Angeles" wrote: Hi , I have used the Debra Dalgleish's Contextures website for instructions on Dependent Lists, and it works excellent , but now I need to add items to my validation Lists , let me explain , if the user type a new element I need to add that new one to the validation list. How can I do it ? Thank you "Ron Coderre" wrote: First, thanks for visiting Debra Dalgleish's Contextures website for instructions on Dependent Lists. Now, I have a lot less expaining to do. Since the dependent list is based on a Named List, try this: With A1 containing the parent list B1 containing the dependent list and the Named list: MyNamedList Then Select B1 <format<conditional formatting Formula is: =ISERROR(MATCH($B$1,MyNamedList,0)) Click the [format] button and set your format Click the [OK] buttons and you're done. Does that help? *********** Regards, Ron XL2002, WinXP "Bob" wrote: Using Data Validation, I have created a dependent list (based on instructions found on the Contextures web site: http://www.contextures.com/xlDataVal02.html). I want to create a conditional format whereby if someone changes the selection in the first data validation list without changing the corresponding selection in the dependent list (i.e., second list), the cell value in the dependent list will turn red. Can anyone tell me how to go about doing this? Thanks, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
Data Validation - Using 2 Lists | Excel Discussion (Misc queries) | |||
Dependent List (via Data Validation) Error | Excel Worksheet Functions | |||
Conditional formatting in a data table | Excel Discussion (Misc queries) | |||
How do I use a conditional (IF) statement in Data Validation? | Excel Worksheet Functions |