Cascading validation (again)

[kcmuppet]

I know this has been addressed a bunch of times, but I can't a solution for where the data are not already organised.

In the following example I'd like to have E1 validated on the countries, and E2 based on the subset of cities corresponding to the country selected in E1. Ideally E1 would not include duplicate entries

Book1
	A	B	C	D	E	F
1	Countries	Cities	.	Selected.Country:	USA	E1.validated.based.on.list.of.Countries
2	USA	New.York	.	Selected.City:	.	How.can.I.validate.E2.based.on.subset.of.cities.that.corresponds.to.value.selected.in.the.E1?
3	USA	Pittsburgh	.	.	.	.
4	France	Paris	.	.	.	.
5	USA	Los.Angeles	.	.	.	.
6	USA	Boston	.	.	Defined.names:	.
7	France	Toulon	.	.	Countries=	OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
8	France	Nice	.	.	Cities=	OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
Sheet1

 

[BrianB]

Here is something that might work, or be modified.
In column C (which can be hidden) put formula :-
=A2 & B2
copied down to create a list of unique values.

Cell E1 can be validated from the list of countries.
Cell E2 can have validation formula :-
=VLOOKUP(E1&E2,C2:C100,1,FALSE)=TRUE
(change the range accordingly)