Data Validation to show only unique values

[bh24524]

I've tried this without success but I am looking for a data validation in a separate tab to show only unique values among a list of approximately 200 entries starting in Cell M5 of a Tab called "New Bid" and ending at M200 . I've tried Offset formulas with CountA in them according to a video I watched but it is not working. How can I do this?

 

[maabadi]

Please Upload Example file & Desired results with XL2BB ADDIN(Preferable) or upload at free hosting Site e.g. www.dropbox.com , GoogleDrive or OneDrive & insert Link here.

 

[Fluff]

How about like

+Fluff v2.xlsm
	A	B	C	D	E
1	County
2	Cumbria		Cumbria		Devon
3	Shropshire		Shropshire
4	Devon		Devon
5	Greater Manchester		Greater Manchester
6	County Durham		County Durham
7	Staffordshire		Staffordshire
8	Buckinghamshire		Buckinghamshire
9	Lancashire		Lancashire
10	North Yorkshire		North Yorkshire
11	West Midlands		West Midlands
12	West Yorkshire		West Yorkshire		Cumbria
13	Cornwall		Cornwall
14	West Yorkshire		Derbyshire
15	Lancashire		Dorset
16	West Yorkshire		Gloucestershire
17	Cumbria		Oxfordshire
18	Derbyshire		Surrey
19	Greater Manchester		Herefordshire
20	Staffordshire		South Yorkshire
21	Staffordshire
22	Derbyshire
23	Devon
24	Dorset
25	Gloucestershire
26	Greater Manchester
27	Greater Manchester
28	Lancashire
29	Oxfordshire
30	Surrey
31	West Midlands
32	West Yorkshire
33	Derbyshire
34	Greater Manchester
35	Herefordshire
36	South Yorkshire
37	West Yorkshire
38	West Yorkshire
39	Greater Manchester
40
Lists

Cell Formulas
Range	Formula
E12	E12	=INDEX(UniqueList,1)
C2:C22	C2	=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$100,C$1:C1,0)))/($A$2:$A$100<>""),1)),"")

Named Ranges
Name	Refers To	Cells
UniqueList	=OFFSET(Lists!$C$2,,,COUNTIF(Lists!$C:$C,"?*"))	E12, C3:C22

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=UniqueList

Ignore the formula in E12, it's just to get XL2BB to show the details of the defined name

 

[bh24524]

How about like

+Fluff v2.xlsm
	A	B	C	D	E
1	County
2	Cumbria		Cumbria		Devon
3	Shropshire		Shropshire
4	Devon		Devon
5	Greater Manchester		Greater Manchester
6	County Durham		County Durham
7	Staffordshire		Staffordshire
8	Buckinghamshire		Buckinghamshire
9	Lancashire		Lancashire
10	North Yorkshire		North Yorkshire
11	West Midlands		West Midlands
12	West Yorkshire		West Yorkshire		Cumbria
13	Cornwall		Cornwall
14	West Yorkshire		Derbyshire
15	Lancashire		Dorset
16	West Yorkshire		Gloucestershire
17	Cumbria		Oxfordshire
18	Derbyshire		Surrey
19	Greater Manchester		Herefordshire
20	Staffordshire		South Yorkshire
21	Staffordshire
22	Derbyshire
23	Devon
24	Dorset
25	Gloucestershire
26	Greater Manchester
27	Greater Manchester
28	Lancashire
29	Oxfordshire
30	Surrey
31	West Midlands
32	West Yorkshire
33	Derbyshire
34	Greater Manchester
35	Herefordshire
36	South Yorkshire
37	West Yorkshire
38	West Yorkshire
39	Greater Manchester
40
Lists

Cell Formulas
Range	Formula
E12	E12	=INDEX(UniqueList,1)
C2:C22	C2	=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$100,C$1:C1,0)))/($A$2:$A$100<>""),1)),"")

Named Ranges
Name	Refers To	Cells
UniqueList	=OFFSET(Lists!$C$2,,,COUNTIF(Lists!$C:$C,"?*"))	E12, C3:C22

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=UniqueList

Ignore the formula in E12, it's just to get XL2BB to show the details of the defined name

Thanks for your help in this. This is what I needed. It's unfortunate that my version doesn't support the Sort function otherwise I would have liked to integrate that into the formula somehow. There isn't any other way to do it besides copying and pasting values and then sorting the values, is there?

 

[Fluff]

Sorting the values would be the simplest, but there are other ways. Have a look here Excel formula: Sort and extract unique values | Exceljet