|
Data Validation
When using dependent dropdowns for data validation, how can I ensure that
when the initial dropdown data is changed that the dependent dropdown has to display a blank field or forces the user to input a new value |
Data Validation
Hi,
Take a look at Debra's brilliant site : http://www.contextures.com/xlDataVal02.html HTH Cheers Carim |
Data Validation
Thanks for that, I've already been there!
To explain further using that site as an example If I change cell A2 from Green Fruit to Red Fruit I would want cell B2 not to show Lime but the list of red fruits "Carim" wrote: Hi, Take a look at Debra's brilliant site : http://www.contextures.com/xlDataVal02.html HTH Cheers Carim |
Data Validation
If I undestand correctly, you would need an event macro with VBA Private Sub Worksheet_Calculate() a module to fire the change of lists in the destination cell, as soon as the first choice has been made in the input cell ... Carim |
Data Validation
There are a couple of sample files with cross dependent validation:
http://www.contextures.com/excelfiles.html Look for 'DV0037 - Dependent Lists Country City' or 'DV0013 - Cross Dependent Validation Lists' guz wrote: Thanks for that, I've already been there! To explain further using that site as an example If I change cell A2 from Green Fruit to Red Fruit I would want cell B2 not to show Lime but the list of red fruits "Carim" wrote: Hi, Take a look at Debra's brilliant site : http://www.contextures.com/xlDataVal02.html HTH Cheers Carim -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Data Validation Changes
After reading many suggestions on this forum on how to ensure that the
correct info is forced to be chosen on the 2nd dropdown cell if the 1st dropdown cell is changed in dependent.multiple drop downs, I am still lost. I am probably even confusing you with that comment - please forgive me - even though I use Excel quite often, I am not that familiar with the intermediate or advanced funstions. I have visited the Contextures website and it is very useful - but again, though I am a quick learner - most of this is over my head! Now to the point, with Contexture instructions, I have created 2 dependant dropdowns using 2 worksheets. Which worked great - but I want to make sure that if they change the first dropdown cell, it will not allow them to keep the info that they chose originally on the 2nd dropdown cell. Below is part of my lists which are listed on the 2nd sheet (with my spreadsheet on the 1st sheet) I think some of my confusion is how to add another date validation souce when I already have one there. This is the formula that I currently have in the 2nd dropdown column on my spreadsheet is =OFFSET(ExpenseStart,MATCH(E6,ExpenseColumn,0)-1,1,COUNTIF(ExpenseColumn,E6),1) I would either like for the 2nd cell to turn blank when the first cell is changed - or for an error alert to pop up asking them to change the 2nd cell also. Please help! Thank you. Expense (Column) Purpose (Column) Expense (List) Breakfast With Customer or Vendor Breakfast Breakfast With employee(s) only Lunch Lunch With Customer or Vendor Dinner Lunch Alone while Traveling Snacks Lunch With employee(s) only Entertainment Dinner With Customer or Vendor Gifts Dinner Alone while Traveling Office Dinner With employee(s) only Promotions Entertainment Tickets or Admission Fee Entertainment Golf fees "Debra Dalgleish" wrote: There are a couple of sample files with cross dependent validation: http://www.contextures.com/excelfiles.html Look for 'DV0037 - Dependent Lists Country City' or 'DV0013 - Cross Dependent Validation Lists' guz wrote: Thanks for that, I've already been there! To explain further using that site as an example If I change cell A2 from Green Fruit to Red Fruit I would want cell B2 not to show Lime but the list of red fruits "Carim" wrote: Hi, Take a look at Debra's brilliant site : http://www.contextures.com/xlDataVal02.html HTH Cheers Carim -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Data Validation Changes
In cell E6, instead of using the Expenses list as the source, use a
conditional list, e.g.: =IF(F6="",Expenses,E6) Then, if a selection has been made in the dependent cell, the only option in cell E6 is the current selection. Delete the entry in cell F6, and the full Expenses list will be available in cell E6. JLowry wrote: After reading many suggestions on this forum on how to ensure that the correct info is forced to be chosen on the 2nd dropdown cell if the 1st dropdown cell is changed in dependent.multiple drop downs, I am still lost. I am probably even confusing you with that comment - please forgive me - even though I use Excel quite often, I am not that familiar with the intermediate or advanced funstions. I have visited the Contextures website and it is very useful - but again, though I am a quick learner - most of this is over my head! Now to the point, with Contexture instructions, I have created 2 dependant dropdowns using 2 worksheets. Which worked great - but I want to make sure that if they change the first dropdown cell, it will not allow them to keep the info that they chose originally on the 2nd dropdown cell. Below is part of my lists which are listed on the 2nd sheet (with my spreadsheet on the 1st sheet) I think some of my confusion is how to add another date validation souce when I already have one there. This is the formula that I currently have in the 2nd dropdown column on my spreadsheet is =OFFSET(ExpenseStart,MATCH(E6,ExpenseColumn,0)-1,1,COUNTIF(ExpenseColumn,E6),1) I would either like for the 2nd cell to turn blank when the first cell is changed - or for an error alert to pop up asking them to change the 2nd cell also. Please help! Thank you. Expense (Column) Purpose (Column) Expense (List) Breakfast With Customer or Vendor Breakfast Breakfast With employee(s) only Lunch Lunch With Customer or Vendor Dinner Lunch Alone while Traveling Snacks Lunch With employee(s) only Entertainment Dinner With Customer or Vendor Gifts Dinner Alone while Traveling Office Dinner With employee(s) only Promotions Entertainment Tickets or Admission Fee Entertainment Golf fees "Debra Dalgleish" wrote: There are a couple of sample files with cross dependent validation: http://www.contextures.com/excelfiles.html Look for 'DV0037 - Dependent Lists Country City' or 'DV0013 - Cross Dependent Validation Lists' guz wrote: Thanks for that, I've already been there! To explain further using that site as an example If I change cell A2 from Green Fruit to Red Fruit I would want cell B2 not to show Lime but the list of red fruits "Carim" wrote: Hi, Take a look at Debra's brilliant site : http://www.contextures.com/xlDataVal02.html HTH Cheers Carim -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Data Validation Changes
See this:
http://tinyurl.com/yw6z87 There is a sample file included that uses an event_change macro to clear the dependent drop down cells. You'll have to enable macros for it to work. Biff "JLowry" wrote in message ... After reading many suggestions on this forum on how to ensure that the correct info is forced to be chosen on the 2nd dropdown cell if the 1st dropdown cell is changed in dependent.multiple drop downs, I am still lost. I am probably even confusing you with that comment - please forgive me - even though I use Excel quite often, I am not that familiar with the intermediate or advanced funstions. I have visited the Contextures website and it is very useful - but again, though I am a quick learner - most of this is over my head! Now to the point, with Contexture instructions, I have created 2 dependant dropdowns using 2 worksheets. Which worked great - but I want to make sure that if they change the first dropdown cell, it will not allow them to keep the info that they chose originally on the 2nd dropdown cell. Below is part of my lists which are listed on the 2nd sheet (with my spreadsheet on the 1st sheet) I think some of my confusion is how to add another date validation souce when I already have one there. This is the formula that I currently have in the 2nd dropdown column on my spreadsheet is =OFFSET(ExpenseStart,MATCH(E6,ExpenseColumn,0)-1,1,COUNTIF(ExpenseColumn,E6),1) I would either like for the 2nd cell to turn blank when the first cell is changed - or for an error alert to pop up asking them to change the 2nd cell also. Please help! Thank you. Expense (Column) Purpose (Column) Expense (List) Breakfast With Customer or Vendor Breakfast Breakfast With employee(s) only Lunch Lunch With Customer or Vendor Dinner Lunch Alone while Traveling Snacks Lunch With employee(s) only Entertainment Dinner With Customer or Vendor Gifts Dinner Alone while Traveling Office Dinner With employee(s) only Promotions Entertainment Tickets or Admission Fee Entertainment Golf fees "Debra Dalgleish" wrote: There are a couple of sample files with cross dependent validation: http://www.contextures.com/excelfiles.html Look for 'DV0037 - Dependent Lists Country City' or 'DV0013 - Cross Dependent Validation Lists' guz wrote: Thanks for that, I've already been there! To explain further using that site as an example If I change cell A2 from Green Fruit to Red Fruit I would want cell B2 not to show Lime but the list of red fruits "Carim" wrote: Hi, Take a look at Debra's brilliant site : http://www.contextures.com/xlDataVal02.html HTH Cheers Carim -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 10:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com