Data Validation Default Based on Cell Value

[williamfr]

Hi all. I'm trying to do something very basic, but i'm not sure if it's possible. I have a column where every field, including the header is a list. When i change the option in the header, is it possible to default the below rows to the same value? ie. If i choose Dallas in the header, all cells in the column change to Dallas. At that point, individual cells can be changed via dropdown to different cities if needed. Then again, if i change the header to Miami..all cells then default to Miami, etc.

I tried googling this but maybe i'm wording it wrong. The first column is just an example of what it would look like when the header is selected. The second column is an example of changing to different selections. The third column is choosing a different header and everything being reset to a different city. Thanks!

Quote Tool.xlsx
	B	C	D	E	F
1	1		2		3
2
3	Dallas		Dallas		Miami
4	Dallas		Dallas		Miami
5	Dallas		Miami		Miami
6	Dallas		Dallas		Miami
7	Dallas		Atlanta		Miami
8	Dallas		Dallas		Miami
9	Dallas		Atlanta		Miami
10	Dallas		Dallas		Miami
11	Dallas		Dallas		Miami
12	Dallas		Baton Rouge		Miami
13	Dallas		Dallas		Miami
14	Dallas		Dallas		Miami
15	Dallas		Dallas		Miami
16	Dallas		Dallas		Miami
17	Dallas		Dallas		Miami
18	Dallas		Dallas		Miami
19	Dallas		Dallas		Miami
20	Dallas		Dallas		Miami
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D3:D20	List	Dallas, Baton Rouge, Atlanta, Miami
B3:B20	List	Dallas, Baton Rouge, Atlanta, Miami
F3:F20	List	Dallas, Baton Rouge, Atlanta, Miami

 

[williamfr]

Sorry i didn't see an edit option but i just wanted to say i figured this out with VBA..but i'd prefer not to use that if i can help it. Thanks again!

 

[Fluff]

I think that VBA is your only option. A formula cannot do what you are asking for.

 

[williamfr]

I think that VBA is your only option. A formula cannot do what you are asking for.

thanks for the confirmation on that!

 

[Fluff]

You're welcome.