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

Hi,

Take a look at Debra's brilliant site :

http://www.contextures.com/xlDataVal02.html

HTH
Cheers
Carim

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

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

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

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

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

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