Sheet with conditional dropdown to select specific range according to choice to change results displayed

[TheJay]

I have a sheet containing data. There are two dropdown boxes. I only want data to display in E4 & F4 once a choice has been made in the dropdown box in C4 AND D4.

When CT is chosen in the first dropdown, I want the subsequent dropdown to be looking through data range D5:D169. If DNN, data range D170:D334.

The difficulty I am having is that some areas have two different "Inspectors" and it's only taking data from the first range, even when DNN is selected.

I'm not sure what to do to get this working.

I have the data listed immediately under the dropdown box in order to utilise autocomplete, so the data will be in hidden rows once this is finished.

Testy.xlsm

drive.google.com

 

[kevin9999]

I think you may be making the dropdown choices for the parish a bit more complicated than it needs to be, given that both the CT and DNN lists are identical. On the other issue, please try putting the following formula in cell F4. It should take care of the different inspector issue for you:

=IFERROR(INDEX(F5:F334,MATCH(1,(C4=C5:C334)*(D4=D5:D334),0)),"")

 

[Armstrong_N14]

how about..

Testyx.xlsm
	A	B	C	D	E	F
1
2			Areas
3			Department	Parish	Parish #	Inspector
4			CT	Birtsmorton	109	Inspector
5
6	CTAbberley	CTAbberley101	CT	Abberley	101	VV
7	CTAlfrick	CTAlfrick103	CT	Alfrick	103	VV
8	CTAstley	CTAstley105	CT	Astley	105	VV
9	CTBayton	CTBayton230	CT	Bayton	230	VV
10	CTBerrow	CTBerrow108	CT	Berrow	108	VV
11	CTBirtsmorton	CTBirtsmorton109	CT	Birtsmorton	109	Inspector
12	CTBockleton	CTBockleton231	CT	Bockleton	231	VV
13	CTBransford	CTBransford112	CT	Bransford	112	VV
14	CTBroadheath	CTBroadheath114	CT	Broadheath	114	VV
15	CTBroadwas	CTBroadwas115	CT	Broadwas	115	VV
16	CTBushley	CTBushley118	CT	Bushley	118	VV
17	CTCastlemorton	CTCastlemorton121	CT	Castlemorton	121	VV
18	CTClifton	CTClifton122	CT	Clifton	122	VV
19	CTCotheridge	CTCotheridge126	CT	Cotheridge	126	VV
20	CTCroome D’Abiot	CTCroome D’Abiot128	CT	Croome D’Abiot	128	VV
21	CTDoddenham	CTDoddenham129	CT	Doddenham	129	VV
22	CTEarls Croome	CTEarls Croome131	CT	Earls Croome	131	VV
23	CTEastham	CTEastham232	CT	Eastham	232	VV
24	CTEldersfield	CTEldersfield136	CT	Eldersfield	136	VV
25	CTGreat Witley	CTGreat Witley139	CT	Great Witley	139	VV
26	CTGrimley	CTGrimley141	CT	Grimley	141	VV
27	CTGuarlford	CTGuarlford142	CT	Guarlford	142	VV
28	CTHallow	CTHallow143	CT	Hallow	143	VV
29	CTHanley	CTHanley233	CT	Hanley	233	VV
30	CTHanley Castle	CTHanley Castle145	CT	Hanley Castle	145	VV
31	CTHill Croome	CTHill Croome146	CT	Hill Croome	146	VV
32	CTHillhampton	CTHillhampton147	CT	Hillhampton	147	VV
33	CTHoldfast	CTHoldfast148	CT	Holdfast	148	VV
34	CTHolt	CTHolt149	CT	Holt	149	VV
35	CTKempsey	CTKempsey150	CT	Kempsey	150	VV
36	CTKenswick	CTKenswick151	CT	Kenswick	151	VV
37	CTKnighton on Teme	CTKnighton on Teme234	CT	Knighton on Teme	234	VV
38	CTKnightwick	CTKnightwick152	CT	Knightwick	152	VV
39	CTKyre	CTKyre235	CT	Kyre	235	VV
40	CTLeigh	CTLeigh155	CT	Leigh	155	VV
41	CTLiDNNidge	CTLiDNNidge236	CT	LiDNNidge	236	VV
42	CTLittle Malvern	CTLittle Malvern158	CT	Little Malvern	158	VV
43	CTLittle Witley	CTLittle Witley160	CT	Little Witley	160	VV
44	CTLongdon	CTLongdon161	CT	Longdon	161	VV
45	CTLower Sapey	CTLower Sapey162	CT	Lower Sapey	162	VV
46	CTLulsley	CTLulsley163	CT	Lulsley	163	VV
47	CTMadresfield	CTMadresfield164	CT	Madresfield	164	VV
48	CTMalvern Wells	CTMalvern Wells166	CT	Malvern Wells	166	VV
49	CTMalvern	CTMalvern165	CT	Malvern	165	VV
50	CTMamble	CTMamble237	CT	Mamble	237	VV
51	CTMartley	CTMartley175	CT	Martley	175	VV
52	CTNewland	CTNewland181	CT	Newland	181	VV
53	CTPendock	CTPendock185	CT	Pendock	185	VV
54	CTPensax	CTPensax238	CT	Pensax	238	VV
55	CTPowick	CTPowick187	CT	Powick	187	VV
56	CTQueen Hill	CTQueen Hill189	CT	Queen Hill	189	VV
57	CTRipple	CTRipple190	CT	Ripple	190	VV
58	CTRockford	CTRockford239	CT	Rockford	239	VV
59	CTRushwick	CTRushwick191	CT	Rushwick	191	VV
60	CTSevern Stoke	CTSevern Stoke193	CT	Severn Stoke	193	VV
61	CTShelsley Beauchamp	CTShelsley Beauchamp194	CT	Shelsley Beauchamp	194	VV
62	CTShelsley King	CTShelsley King195	CT	Shelsley King	195	VV
63	CTShelsley Walsh	CTShelsley Walsh196	CT	Shelsley Walsh	196	VV
64	CTShrawley	CTShrawley197	CT	Shrawley	197	VV
65	CTStanford w/orleton	CTStanford w/orleton240	CT	Stanford w/orleton	240	VV
66	CTStockton-on-Teme	CTStockton-on-Teme241	CT	Stockton-on-Teme	241	VV
67	CTStoke Bliss	CTStoke Bliss242	CT	Stoke Bliss	242	VV
68	CTSuckley	CTSuckley201	CT	Suckley	201	VV
69	CTTenbury	CTTenbury243	CT	Tenbury	243	VV
70	CTUpton on Severn	CTUpton on Severn208/209	CT	Upton on Severn	208/209	VV
71	CTWelland	CTWelland211	CT	Welland	211	VV
72	CTWest Malvern	CTWest Malvern167	CT	West Malvern	167	VV
73	CTWichenford	CTWichenford214	CT	Wichenford	214	VV
74	CTWorcester	CTWorcester10	CT	Worcester	10	NNN
75	CTWarndon	CTWarndon20	CT	Warndon	20	NNN
76	CTSt Peters	CTSt Peters30	CT	St Peters	30	NNN
77	CTAbberton	CTAbberton1	CT	Abberton	001	NNN
78	CTAbbots Morton	CTAbbots Morton45	CT	Abbots Morton	045	NNN
79	CTAldington	CTAldington46	CT	Aldington	046	NNN
80	CTAshton Under Hill	CTAshton Under Hill47	CT	Ashton Under Hill	047	VV
81	CTAston Somerville	CTAston Somerville48	CT	Aston Somerville	048	VV
82	CTBadsey	CTBadsey49	CT	Badsey	049	NNN
83	CTBeckford	CTBeckford51	CT	Beckford	051	VV
84	CTBesford	CTBesford2	CT	Besford	002	VV
85	CTBickmarsh	CTBickmarsh52	CT	Bickmarsh	052	NNN
86	CTBirlingham	CTBirlingham3	CT	Birlingham	003	VV
87	CTBishampton	CTBishampton4	CT	Bishampton	004	NNN
88	CTBredicot	CTBredicot5	CT	Bredicot	005	NNN
89	CTBredon	CTBredon6	CT	Bredon	006	VV
90	CTBredons Norton	CTBredons Norton7	CT	Bredons Norton	007	VV
91	CTBretforton	CTBretforton53	CT	Bretforton	053	NNN
92	CTBricklehampton	CTBricklehampton8	CT	Bricklehampton	008	NNN
93	CTBroadway	CTBroadway54	CT	Broadway	054	NNN
94	CTBroughton Hackett	CTBroughton Hackett9	CT	Broughton Hackett	009	NNN
95	CTCharlton	CTCharlton10	CT	Charlton	010	NNN
96	CTChildswickham	CTChildswickham55	CT	Childswickham	055	NNN
97	CTSouth Lenches	CTSouth Lenches56	CT	South Lenches	056	NNN
98	CTChurchill	CTChurchill11	CT	Churchill	011	NNN
99	CTCleeve Prior	CTCleeve Prior57	CT	Cleeve Prior	057	NNN
100	CTConderton	CTConderton58	CT	Conderton	058	VV
101	CTCookhill	CTCookhill71	CT	Cookhill	071	NNN
102	CTCropthorne	CTCropthorne14	CT	Cropthorne	014	NNN
103	CTCrowle	CTCrowle75	CT	Crowle	075	NNN
104	CTDefford	CTDefford15	CT	Defford	015	VV
105	CTDodderhill	CTDodderhill76	CT	Dodderhill	076	NNN
106	CTDormston	CTDormston16	CT	Dormston	016	NNN
107	CTDoverdale	CTDoverdale77	CT	Doverdale	077	NNN
108	CTDrakes Broughton	CTDrakes Broughton17	CT	Drakes Broughton	017	NNN
109	CTDroitwich	CTDroitwich95	CT	Droitwich	095	NNN
110	CTEckington	CTEckington19	CT	Eckington	019	VV
111	CTElmbridge	CTElmbridge78	CT	Elmbridge	078	NNN
112	CTElmley Castle	CTElmley Castle20	CT	Elmley Castle	020	NNN
113	CTElmley Lovett	CTElmley Lovett79	CT	Elmley Lovett	079	NNN
114	CTEvesham	CTEvesham50	CT	Evesham	050	VV
115	CTFladbury	CTFladbury21	CT	Fladbury	021	NNN
116	CTFlyford Flavel	CTFlyford Flavel22	CT	Flyford Flavel	022	NNN
117	CTGrafton Flyford	CTGrafton Flyford23	CT	Grafton Flyford	023	NNN
118	CTGreat Comberton	CTGreat Comberton12	CT	Great Comberton	012	NNN
119	CTHadzor	CTHadzor80	CT	Hadzor	080	NNN
120	CTHampton Lovett	CTHampton Lovett81	CT	Hampton Lovett	081	NNN
121	CTHanbury	CTHanbury82	CT	Hanbury	082	NNN
122	CTHartlebury	CTHartlebury83	CT	Hartlebury	083	NNN
123	CTHarvington	CTHarvington59	CT	Harvington	059	NNN
124	CTHill & Moor	CTHill & Moor24	CT	Hill & Moor	024	NNN
125	CTHimbleton	CTHimbleton84	CT	Himbleton	084	NNN
126	CTHindlip	CTHindlip85	CT	Hindlip	085	NNN
127	CTHinton on The Green	CTHinton on The Green72	CT	Hinton on The Green	072	NNN
128	CTHoneybourne	CTHoneybourne60	CT	Honeybourne	060	NNN
129	CTHuddington	CTHuddington86	CT	Huddington	086	NNN
130	CTInkberrow	CTInkberrow61	CT	Inkberrow	061	NNN
131	CTKemerton	CTKemerton62	CT	Kemerton	062	VV
132	CTKington	CTKington25	CT	Kington	025	NNN
133	CTLittle Comberton	CTLittle Comberton13	CT	Little Comberton	013	NNN
134	CTMartin Hussingtree	CTMartin Hussingtree87	CT	Martin Hussingtree	087	NNN
135	CTMiddle Littleton	CTMiddle Littleton 63	CT	Middle Littleton	063	NNN
136	CTNaunton Beauchamp	CTNaunton Beauchamp26	CT	Naunton Beauchamp	026	NNN
137	CTNetherton	CTNetherton27	CT	Netherton	027	NNN
138	CTNorth and Middle Littleton	CTNorth and Middle Littleton63	CT	North and Middle Littleton	063	NNN
139	CTNorth Claines	CTNorth Claines74	CT	North Claines	074	NNN
140	CTNorth Piddle	CTNorth Piddle28	CT	North Piddle	028	NNN
141	CTNorton and Lenchwick	CTNorton and Lenchwick64	CT	Norton and Lenchwick	064	NNN
142	CTNorton Juxta Kempsey	CTNorton Juxta Kempsey29	CT	Norton Juxta Kempsey	029	NNN
143	CTOddingley	CTOddingley88	CT	Oddingley	088	NNN
144	CTOffenham	CTOffenham65	CT	Offenham	065	NNN
145	CTOmbersley	CTOmbersley89	CT	Ombersley	089	NNN
146	CTOverbury	CTOverbury73	CT	Overbury	073	VV
147	CTPebworth	CTPebworth66	CT	Pebworth	066	NNN
148	CTPeopleton	CTPeopleton30	CT	Peopleton	030	NNN
149	CTPershore	CTPershore34	CT	Pershore	034	NNN
150	CTPinvin	CTPinvin32	CT	Pinvin	032	NNN
151	CTPirton	CTPirton33	CT	Pirton	033	VV
152	CTRous Lench	CTRous Lench67	CT	Rous Lench	067	NNN
153	CTSalwarpe	CTSalwarpe90	CT	Salwarpe	090	NNN
154	CTSedgeberrow	CTSedgeberrow68	CT	Sedgeberrow	068	VV
155	CTSouth Littleton	CTSouth Littleton69	CT	South Littleton	069	NNN
156	CTSpetchley	CTSpetchley35	CT	Spetchley	035	NNN
157	CTStock and Bradley	CTStock and Bradley91	CT	Stock and Bradley	091	NNN
158	CTStoulton	CTStoulton36	CT	Stoulton	036	NNN
159	CTStrensham	CTStrensham37	CT	Strensham	037	VV
160	CTThrockmorton	CTThrockmorton38	CT	Throckmorton	038	NNN
161	CTTibberton	CTTibberton92	CT	Tibberton	092	NNN
162	CTUpton Snodsbury	CTUpton Snodsbury39	CT	Upton Snodsbury	039	NNN
163	CTUpton Warren	CTUpton Warren93	CT	Upton Warren	093	NNN
164	CTWadborough	CTWadborough18	CT	Wadborough	018	NNN
165	CTWestwood	CTWestwood94	CT	Westwood	094	NNN
166	CTWhite Ladies Aston	CTWhite Ladies Aston40	CT	White Ladies Aston	040	NNN
167	CTWhittington	CTWhittington41	CT	Whittington	041	NNN
168	CTWick	CTWick42	CT	Wick	042	NNN
169	CTWickhamford	CTWickhamford70	CT	Wickhamford	070	NNN
170	CTWyre Piddle	CTWyre Piddle43	CT	Wyre Piddle	043	NNN
171	CTAbberley	CTAbberley101	DNN	Abberley	101	VV
172	CTAlfrick	CTAlfrick103	DNN	Alfrick	103	VV
173	CTAstley	CTAstley105	DNN	Astley	105	VV
174	CTBayton	CTBayton230	DNN	Bayton	230	VV
175	CTBerrow	CTBerrow108	DNN	Berrow	108	VV
176	CTBirtsmorton	CTBirtsmorton109	DNN	Birtsmorton	109	VV
177	CTBockleton	CTBockleton231	DNN	Bockleton	231	VV
178	CTBransford	CTBransford112	DNN	Bransford	112	VV
179	CTBroadheath	CTBroadheath114	DNN	Broadheath	114	VV
VO Areas

Cell Formulas
Range	Formula
E4	E4	=VLOOKUP($C$4&$D$4,$A$6:$G$335,5,0)
F4	F4	=VLOOKUP(C4&D4&E4,$B$6:$G$335,5,0)
A6:A179	A6	=$C$4&D6
B6:B179	B6	=$C$4&D6&E6

Named Ranges
Name	Refers To	Cells
'VO Areas'!_FilterDatabase	='VO Areas'!$C$6:$F$335	E4:F4
CT	='VO Areas'!$D$6:$D$170	E4:F4, A6:B6
DNN	='VO Areas'!$D$171:$D$335	A171:B171, E4:F4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:D4	Cell Value	contains "Choose Parish"	text	NO
E4:F4,C4	Expression	=ISNA(C4)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C4	List	CT,DNN
D4	List	=IF(C4="Council Tax",INDIRECT("D5:D169"),INDIRECT("D170:D334"))
E4	List	=$E$6:$E$169
F4	List	=$F$6:$F$169

 

[Armstrong_N14]

i omitted some data due to XLBB limits

 

[TheJay]

Thanks for your replies. The first answer seemed to work. I couldn't figure out XL2BB to test the other.

It's been developed further now and it's nearly done. I'm not sure how to make the last bit work.

Testy.xlsm

drive.google.com

E4 is now a validation drop down box but also I need it to update when a selection is made in C4 and D4 and C4 and D4 to be updated if a selection is made from E4.

In the file I have uploaded, I have left choices in C4 and D4. When this is done, E4 should display the parish of the data on that row. Equally, if the user bypasses C4 and D4 and enters or chooses a Parish in E4, it should populate the D4 with the corresponding parish name, whilst not displaying anything in F4 and G4 until C4 has a value.

Is this possible?

 

[TheJay]

Sorry, wrong link. New sheet.

VOP.xlsm

drive.google.com

 

[Armstrong_N14]

how about..

Testyx.xlsm
	A	B	C	D	E	F
1
2			Areas
3			Department	Parish	Parish #	Inspector
4			CT	Birtsmorton	109	Inspector
5
6	CTAbberley	CTAbberley101	CT	Abberley	101	VV
7	CTAlfrick	CTAlfrick103	CT	Alfrick	103	VV
8	CTAstley	CTAstley105	CT	Astley	105	VV
9	CTBayton	CTBayton230	CT	Bayton	230	VV
10	CTBerrow	CTBerrow108	CT	Berrow	108	VV
11	CTBirtsmorton	CTBirtsmorton109	CT	Birtsmorton	109	Inspector
12	CTBockleton	CTBockleton231	CT	Bockleton	231	VV
13	CTBransford	CTBransford112	CT	Bransford	112	VV
14	CTBroadheath	CTBroadheath114	CT	Broadheath	114	VV
15	CTBroadwas	CTBroadwas115	CT	Broadwas	115	VV
16	CTBushley	CTBushley118	CT	Bushley	118	VV
17	CTCastlemorton	CTCastlemorton121	CT	Castlemorton	121	VV
18	CTClifton	CTClifton122	CT	Clifton	122	VV
19	CTCotheridge	CTCotheridge126	CT	Cotheridge	126	VV
20	CTCroome D’Abiot	CTCroome D’Abiot128	CT	Croome D’Abiot	128	VV
21	CTDoddenham	CTDoddenham129	CT	Doddenham	129	VV
22	CTEarls Croome	CTEarls Croome131	CT	Earls Croome	131	VV
23	CTEastham	CTEastham232	CT	Eastham	232	VV
24	CTEldersfield	CTEldersfield136	CT	Eldersfield	136	VV
25	CTGreat Witley	CTGreat Witley139	CT	Great Witley	139	VV
26	CTGrimley	CTGrimley141	CT	Grimley	141	VV
27	CTGuarlford	CTGuarlford142	CT	Guarlford	142	VV
28	CTHallow	CTHallow143	CT	Hallow	143	VV
29	CTHanley	CTHanley233	CT	Hanley	233	VV
30	CTHanley Castle	CTHanley Castle145	CT	Hanley Castle	145	VV
31	CTHill Croome	CTHill Croome146	CT	Hill Croome	146	VV
32	CTHillhampton	CTHillhampton147	CT	Hillhampton	147	VV
33	CTHoldfast	CTHoldfast148	CT	Holdfast	148	VV
34	CTHolt	CTHolt149	CT	Holt	149	VV
35	CTKempsey	CTKempsey150	CT	Kempsey	150	VV
36	CTKenswick	CTKenswick151	CT	Kenswick	151	VV
37	CTKnighton on Teme	CTKnighton on Teme234	CT	Knighton on Teme	234	VV
38	CTKnightwick	CTKnightwick152	CT	Knightwick	152	VV
39	CTKyre	CTKyre235	CT	Kyre	235	VV
40	CTLeigh	CTLeigh155	CT	Leigh	155	VV
41	CTLiDNNidge	CTLiDNNidge236	CT	LiDNNidge	236	VV
42	CTLittle Malvern	CTLittle Malvern158	CT	Little Malvern	158	VV
43	CTLittle Witley	CTLittle Witley160	CT	Little Witley	160	VV
44	CTLongdon	CTLongdon161	CT	Longdon	161	VV
45	CTLower Sapey	CTLower Sapey162	CT	Lower Sapey	162	VV
46	CTLulsley	CTLulsley163	CT	Lulsley	163	VV
47	CTMadresfield	CTMadresfield164	CT	Madresfield	164	VV
48	CTMalvern Wells	CTMalvern Wells166	CT	Malvern Wells	166	VV
49	CTMalvern	CTMalvern165	CT	Malvern	165	VV
50	CTMamble	CTMamble237	CT	Mamble	237	VV
51	CTMartley	CTMartley175	CT	Martley	175	VV
52	CTNewland	CTNewland181	CT	Newland	181	VV
53	CTPendock	CTPendock185	CT	Pendock	185	VV
54	CTPensax	CTPensax238	CT	Pensax	238	VV
55	CTPowick	CTPowick187	CT	Powick	187	VV
56	CTQueen Hill	CTQueen Hill189	CT	Queen Hill	189	VV
57	CTRipple	CTRipple190	CT	Ripple	190	VV
58	CTRockford	CTRockford239	CT	Rockford	239	VV
59	CTRushwick	CTRushwick191	CT	Rushwick	191	VV
60	CTSevern Stoke	CTSevern Stoke193	CT	Severn Stoke	193	VV
61	CTShelsley Beauchamp	CTShelsley Beauchamp194	CT	Shelsley Beauchamp	194	VV
62	CTShelsley King	CTShelsley King195	CT	Shelsley King	195	VV
63	CTShelsley Walsh	CTShelsley Walsh196	CT	Shelsley Walsh	196	VV
64	CTShrawley	CTShrawley197	CT	Shrawley	197	VV
65	CTStanford w/orleton	CTStanford w/orleton240	CT	Stanford w/orleton	240	VV
66	CTStockton-on-Teme	CTStockton-on-Teme241	CT	Stockton-on-Teme	241	VV
67	CTStoke Bliss	CTStoke Bliss242	CT	Stoke Bliss	242	VV
68	CTSuckley	CTSuckley201	CT	Suckley	201	VV
69	CTTenbury	CTTenbury243	CT	Tenbury	243	VV
70	CTUpton on Severn	CTUpton on Severn208/209	CT	Upton on Severn	208/209	VV
71	CTWelland	CTWelland211	CT	Welland	211	VV
72	CTWest Malvern	CTWest Malvern167	CT	West Malvern	167	VV
73	CTWichenford	CTWichenford214	CT	Wichenford	214	VV
74	CTWorcester	CTWorcester10	CT	Worcester	10	NNN
75	CTWarndon	CTWarndon20	CT	Warndon	20	NNN
76	CTSt Peters	CTSt Peters30	CT	St Peters	30	NNN
77	CTAbberton	CTAbberton1	CT	Abberton	001	NNN
78	CTAbbots Morton	CTAbbots Morton45	CT	Abbots Morton	045	NNN
79	CTAldington	CTAldington46	CT	Aldington	046	NNN
80	CTAshton Under Hill	CTAshton Under Hill47	CT	Ashton Under Hill	047	VV
81	CTAston Somerville	CTAston Somerville48	CT	Aston Somerville	048	VV
82	CTBadsey	CTBadsey49	CT	Badsey	049	NNN
83	CTBeckford	CTBeckford51	CT	Beckford	051	VV
84	CTBesford	CTBesford2	CT	Besford	002	VV
85	CTBickmarsh	CTBickmarsh52	CT	Bickmarsh	052	NNN
86	CTBirlingham	CTBirlingham3	CT	Birlingham	003	VV
87	CTBishampton	CTBishampton4	CT	Bishampton	004	NNN
88	CTBredicot	CTBredicot5	CT	Bredicot	005	NNN
89	CTBredon	CTBredon6	CT	Bredon	006	VV
90	CTBredons Norton	CTBredons Norton7	CT	Bredons Norton	007	VV
91	CTBretforton	CTBretforton53	CT	Bretforton	053	NNN
92	CTBricklehampton	CTBricklehampton8	CT	Bricklehampton	008	NNN
93	CTBroadway	CTBroadway54	CT	Broadway	054	NNN
94	CTBroughton Hackett	CTBroughton Hackett9	CT	Broughton Hackett	009	NNN
95	CTCharlton	CTCharlton10	CT	Charlton	010	NNN
96	CTChildswickham	CTChildswickham55	CT	Childswickham	055	NNN
97	CTSouth Lenches	CTSouth Lenches56	CT	South Lenches	056	NNN
98	CTChurchill	CTChurchill11	CT	Churchill	011	NNN
99	CTCleeve Prior	CTCleeve Prior57	CT	Cleeve Prior	057	NNN
100	CTConderton	CTConderton58	CT	Conderton	058	VV
101	CTCookhill	CTCookhill71	CT	Cookhill	071	NNN
102	CTCropthorne	CTCropthorne14	CT	Cropthorne	014	NNN
103	CTCrowle	CTCrowle75	CT	Crowle	075	NNN
104	CTDefford	CTDefford15	CT	Defford	015	VV
105	CTDodderhill	CTDodderhill76	CT	Dodderhill	076	NNN
106	CTDormston	CTDormston16	CT	Dormston	016	NNN
107	CTDoverdale	CTDoverdale77	CT	Doverdale	077	NNN
108	CTDrakes Broughton	CTDrakes Broughton17	CT	Drakes Broughton	017	NNN
109	CTDroitwich	CTDroitwich95	CT	Droitwich	095	NNN
110	CTEckington	CTEckington19	CT	Eckington	019	VV
111	CTElmbridge	CTElmbridge78	CT	Elmbridge	078	NNN
112	CTElmley Castle	CTElmley Castle20	CT	Elmley Castle	020	NNN
113	CTElmley Lovett	CTElmley Lovett79	CT	Elmley Lovett	079	NNN
114	CTEvesham	CTEvesham50	CT	Evesham	050	VV
115	CTFladbury	CTFladbury21	CT	Fladbury	021	NNN
116	CTFlyford Flavel	CTFlyford Flavel22	CT	Flyford Flavel	022	NNN
117	CTGrafton Flyford	CTGrafton Flyford23	CT	Grafton Flyford	023	NNN
118	CTGreat Comberton	CTGreat Comberton12	CT	Great Comberton	012	NNN
119	CTHadzor	CTHadzor80	CT	Hadzor	080	NNN
120	CTHampton Lovett	CTHampton Lovett81	CT	Hampton Lovett	081	NNN
121	CTHanbury	CTHanbury82	CT	Hanbury	082	NNN
122	CTHartlebury	CTHartlebury83	CT	Hartlebury	083	NNN
123	CTHarvington	CTHarvington59	CT	Harvington	059	NNN
124	CTHill & Moor	CTHill & Moor24	CT	Hill & Moor	024	NNN
125	CTHimbleton	CTHimbleton84	CT	Himbleton	084	NNN
126	CTHindlip	CTHindlip85	CT	Hindlip	085	NNN
127	CTHinton on The Green	CTHinton on The Green72	CT	Hinton on The Green	072	NNN
128	CTHoneybourne	CTHoneybourne60	CT	Honeybourne	060	NNN
129	CTHuddington	CTHuddington86	CT	Huddington	086	NNN
130	CTInkberrow	CTInkberrow61	CT	Inkberrow	061	NNN
131	CTKemerton	CTKemerton62	CT	Kemerton	062	VV
132	CTKington	CTKington25	CT	Kington	025	NNN
133	CTLittle Comberton	CTLittle Comberton13	CT	Little Comberton	013	NNN
134	CTMartin Hussingtree	CTMartin Hussingtree87	CT	Martin Hussingtree	087	NNN
135	CTMiddle Littleton	CTMiddle Littleton 63	CT	Middle Littleton	063	NNN
136	CTNaunton Beauchamp	CTNaunton Beauchamp26	CT	Naunton Beauchamp	026	NNN
137	CTNetherton	CTNetherton27	CT	Netherton	027	NNN
138	CTNorth and Middle Littleton	CTNorth and Middle Littleton63	CT	North and Middle Littleton	063	NNN
139	CTNorth Claines	CTNorth Claines74	CT	North Claines	074	NNN
140	CTNorth Piddle	CTNorth Piddle28	CT	North Piddle	028	NNN
141	CTNorton and Lenchwick	CTNorton and Lenchwick64	CT	Norton and Lenchwick	064	NNN
142	CTNorton Juxta Kempsey	CTNorton Juxta Kempsey29	CT	Norton Juxta Kempsey	029	NNN
143	CTOddingley	CTOddingley88	CT	Oddingley	088	NNN
144	CTOffenham	CTOffenham65	CT	Offenham	065	NNN
145	CTOmbersley	CTOmbersley89	CT	Ombersley	089	NNN
146	CTOverbury	CTOverbury73	CT	Overbury	073	VV
147	CTPebworth	CTPebworth66	CT	Pebworth	066	NNN
148	CTPeopleton	CTPeopleton30	CT	Peopleton	030	NNN
149	CTPershore	CTPershore34	CT	Pershore	034	NNN
150	CTPinvin	CTPinvin32	CT	Pinvin	032	NNN
151	CTPirton	CTPirton33	CT	Pirton	033	VV
152	CTRous Lench	CTRous Lench67	CT	Rous Lench	067	NNN
153	CTSalwarpe	CTSalwarpe90	CT	Salwarpe	090	NNN
154	CTSedgeberrow	CTSedgeberrow68	CT	Sedgeberrow	068	VV
155	CTSouth Littleton	CTSouth Littleton69	CT	South Littleton	069	NNN
156	CTSpetchley	CTSpetchley35	CT	Spetchley	035	NNN
157	CTStock and Bradley	CTStock and Bradley91	CT	Stock and Bradley	091	NNN
158	CTStoulton	CTStoulton36	CT	Stoulton	036	NNN
159	CTStrensham	CTStrensham37	CT	Strensham	037	VV
160	CTThrockmorton	CTThrockmorton38	CT	Throckmorton	038	NNN
161	CTTibberton	CTTibberton92	CT	Tibberton	092	NNN
162	CTUpton Snodsbury	CTUpton Snodsbury39	CT	Upton Snodsbury	039	NNN
163	CTUpton Warren	CTUpton Warren93	CT	Upton Warren	093	NNN
164	CTWadborough	CTWadborough18	CT	Wadborough	018	NNN
165	CTWestwood	CTWestwood94	CT	Westwood	094	NNN
166	CTWhite Ladies Aston	CTWhite Ladies Aston40	CT	White Ladies Aston	040	NNN
167	CTWhittington	CTWhittington41	CT	Whittington	041	NNN
168	CTWick	CTWick42	CT	Wick	042	NNN
169	CTWickhamford	CTWickhamford70	CT	Wickhamford	070	NNN
170	CTWyre Piddle	CTWyre Piddle43	CT	Wyre Piddle	043	NNN
171	CTAbberley	CTAbberley101	DNN	Abberley	101	VV
172	CTAlfrick	CTAlfrick103	DNN	Alfrick	103	VV
173	CTAstley	CTAstley105	DNN	Astley	105	VV
174	CTBayton	CTBayton230	DNN	Bayton	230	VV
175	CTBerrow	CTBerrow108	DNN	Berrow	108	VV
176	CTBirtsmorton	CTBirtsmorton109	DNN	Birtsmorton	109	VV
177	CTBockleton	CTBockleton231	DNN	Bockleton	231	VV
178	CTBransford	CTBransford112	DNN	Bransford	112	VV
179	CTBroadheath	CTBroadheath114	DNN	Broadheath	114	VV
VO Areas

Cell Formulas
Range	Formula
E4	E4	=VLOOKUP($C$4&$D$4,$A$6:$G$335,5,0)
F4	F4	=VLOOKUP(C4&D4&E4,$B$6:$G$335,5,0)
A6:A179	A6	=$C$4&D6
B6:B179	B6	=$C$4&D6&E6

Named Ranges
Name	Refers To	Cells
'VO Areas'!_FilterDatabase	='VO Areas'!$C$6:$F$335	E4:F4
CT	='VO Areas'!$D$6:$D$170	E4:F4, A6:B6
DNN	='VO Areas'!$D$171:$D$335	A171:B171, E4:F4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:D4	Cell Value	contains "Choose Parish"	text	NO
E4:F4,C4	Expression	=ISNA(C4)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C4	List	CT,DNN
D4	List	=IF(C4="Council Tax",INDIRECT("D5:D169"),INDIRECT("D170:D334"))
E4	List	=$E$6:$E$169
F4	List	=$F$6:$F$169

you dont need the xlbb for this to work. just click the copy icon on the upper left and paste it directly to excel

 

[TheJay]

And that copies all the conditional formatting and all other settings etc? So copying that picks everything up?

The one line of code presented made it work for me, it's been overhauled since then and it's close to being finished. I need to know about the functionality I mentioned before in the new sheet I uploaded.

Thanks.

 

[Armstrong_N14]

And that copies all the conditional formatting and all other settings etc? So copying that picks everything up?

The one line of code presented made it work for me, it's been overhauled since then and it's close to being finished. I need to know about the functionality I mentioned before in the new sheet I uploaded.

Thanks.

yes. either way, glad that you've figured it out. good day ahead.

 

[TheJay]

Hopefully this last bit can be solved fairly quickly as in the latest attached link.