Conditional data validation drop-down lists

quemuenchatocha

Board Regular
Joined
Aug 4, 2021
Messages
50
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Cordial greetings

I have two concerns and they are as follows.

The first one is, in column A are registered 6 States with their respective cities, the name of each State is in capital letters, now, I need to extract in a separate column (can be for example column L) only the names corresponding to the 6 States, use the function FILTER, ISBLANK and EXACT(UPPER), but I can not get the expected results. Is it possible to obtain this list of only the States in a separate column?

The second query has to do with the longitude and latitude values for each of the cities. For cells H3 and H7 are the values of the States while in cells H4 and H8 are the cities for each of the states recorded in a data list.

For example, in cells I4 and J4 I get the data corresponding to the longitude and latitude respectively for the State and city chosen in cells H3 and H4, the problem lies for those cities in different States that have the same name, for example, if I want to do this same calculation, but this time for the city of Greenville in the State of North Carolina (or Texas, Tennessee, South Carolina) in cells I8 and J8, I get the same results obtained for the city of Greenville but in the State of Illinois.

Is there any formula where it is possible to calculate the corresponding values for each of the cities in your respective state?

Libro1
ABCDEFGHIJKL
1ALABAMALATITUDELONGITUDE
2Alexander City32,9585,95Starting LocationLATLONStates
3Anniston33,5885,85StateILLINOISALABAMA
4Auburn32,6085,50CityGreenville38,883333389,4ILLINOIS
5Birmingham33,5786,75NORTH CAROLINA
6Decatur34,6286,98Ending LocationSOUTH CAROLINA
7Dothan31,3285,45StateNORTH CAROLINATENNESSEE
8Florence34,8087,67CityGreenville38,883333389,4TEXAS
9Gadsden34,0286,00
10Huntsville34,7086,58
11Mobile30,6888,25
12Mobile Co30,6788,25
13Montgomery32,3886,37
14Selma-Craig AFB32,3387,98
15Talladega33,4586,1
16Tuscaloosa33,21787,617
17
18ILLINOISLATITUDELONGITUDE
19Aurora41,7588,33
20Belleville, Scott AFB38,5589,85
21Bloomington40,4888,95
22Carbondale37,7889,25
23Champaign/Urbana40,0388,28
24Chicago, Midway41,7887,75
25Chicago, O'Hare41,9887,90
26Chicago Co41,8887,63
27Danville40,2087,60
28Decatur39,8388,87
29Dixon41,8389,48
30Elgin42,0388,27
31Freeport42,3089,62
32Galesburg40,9390,43
33Greenville38,8889,40
34Joliet41,5288,17
35Kankakee41,0887,92
36La Salle/Peru41,3289,10
37Macomb40,4790,67
38Moline41,4590,52
39Mt Vernon38,3288,87
40Peoria40,6789,68
41Quincy39,9591,20
42Rantoul, Chanute AFB40,3088,13
43Rockford42,3589,05
44Springfield39,8389,67
45Waukegan42,3587,88
46
47NORTH CAROLINALATITUDELONGITUDE
48Asheville35,4382,53
49Charlotte35,2280,93
50Durham35,8778,78
51Elizabeth City36,2776,18
52Fayetteville, Pope AFB35,1779,02
53Goldsboro,Seymour-Johnson35,3377,97
54Greensboro36,0879,95
55Greenville35,6277,42
56Henderson36,3778,42
57Hickory35,7581,38
58Jacksonville34,8377,62
59Lumberton34,6279,07
60New Bern35,0877,05
61Raleigh/Durham35,8778,78
62Rocky Mount35,9777,80
63Wilmington34,2777,92
64Winston-Salem36,1380,22
65
66SOUTH CAROLINALATITUDELONGITUDE
67Anderson34,5082,72
68Charleston AFB32,9080,03
69Charleston Co32,9079,97
70Columbia33,9581,12
71Florence34,1879,72
72Georgetown33,3879,28
73Greenville34,9082,22
74Greenwood34,1782,12
75Orangeburg33,5080,87
76Rock Hill34,9880,97
77Spartanburg34,9782,00
78Sumter, Shaw AFB33,9080,37
79
80TENNESSEELATITUDELONGITUDE
81Athens35,4384,58
82Bristol-Tri City36,4882,40
83Chattanooga35,0385,20
84Clarksville36,5587,37
85Columbia35,6387,03
86Dyersburg36,0289,40
87Greenville36,0782,83
88Jackson35,6088,92
89Knoxville35,8283,98
90Memphis35,0590,00
91Murfreesboro34,9286,47
92Nashville36,1286,68
93Tullahoma35,3886,08
94
95TEXASLATITUDELONGITUDE
96Abilene32,4299,68
97Alice27,7398,03
98Amarillo35,23100,70
99Austin30,3097,70
100Bay City29,0095,97
101Beaumont29,9594,02
102Beeville28,3797,67
103Big Spring32,30101,45
104Brownsville25,9097,43
105Brownwood31,8098,95
106Bryan30,6796,55
107Corpus Christi27,7797,50
108Corsicana32,0896,47
109Dallas32,8596,85
110Del Rio, Laughlin AFB29,37100,78
111Denton33,2097,10
112Eagle Pass28,87100,53
113El Paso31,80106,40
114Fort Worth32,8397,05
115Galveston29,3094,80
116Greenville33,0796,05
117Harlingen26,2397,65
118Houston29,9795,35
119Houston Co29,9895,37
120Huntsville30,7295,55
121Killeen, Robert Gray AAF31,0897,68
122Lamesa32,70101,93
123Laredo AFB27,5399,45
124Longview32,4794,73
125Lubbock33,65101,82
126Lufkin31,4294,80
127Mcallen26,2098,22
128Midland31,95102,18
129Mineral Wells32,7898,07
130Palestine Co31,7895,63
131Pampa35,53100,98
132Pecos31,42103,50
133Plainview34,18101,70
134Port Arthur29,9594,02
135Goodfellow AFB31,43100,40
136San Antonio29,5398,47
137Sherman, Perrin AFB33,7296,67
138Snyder32,72100,92
139Temple31,1097,35
140Tyler32,3595,27
141Vernon34,1799,30
142Victoria28,8596,92
143Waco31,6297,22
144Wichita Falls33,9798,48
Hoja1
Cell Formulas
RangeFormula
I4I4=INDEX(B:B,MATCH($H$4,A:A,0))
J4J4=INDEX(C:C,MATCH($H$4,A:A,0))
I8I8=INDEX(B:B,MATCH($H$8,A:A,0))
J8J8=INDEX(C:C,MATCH($H$8,A:A,0))
Named Ranges
NameRefers ToCells
ALABAMA=Hoja1!$A$2:$A$16I8:J8, I4:J4
ILLINOIS=Hoja1!$A$19:$A$45I8:J8, I4:J4
NORTH_CAROLINA=Hoja1!$A$48:$A$64I8:J8, I4:J4
SOUTH_CAROLINA=Hoja1!$A$67:$A$78I8:J8, I4:J4
TENNESSEE=Hoja1!$A$81:$A$93I8:J8, I4:J4
TEXAS=Hoja1!$A$96:$A$144I8:J8, I4:J4
Cells with Data Validation
CellAllowCriteria
H3List=$L$3:$L$8
H4List=INDIRECTO(SUSTITUIR($H$3;" ";"_"))
H7List=$L$3:$L$8
H8List=INDIRECTO(SUSTITUIR($H$7;" ";"_"))


Thank you very much for your attention and help with any hints on a formula that can be used to obtain the results!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
First question - list the states

Try in L3
=IFERROR(INDEX(A$1:A$1000,AGGREGATE(15,6,(ROW(A$1:A$1000)-ROW(A$1)+1)/(EXACT(A$1:A$1000,UPPER(A$1:A$1000))*(A$1:A$1000<>"")),ROWS(L$3:L3))),"")
copy down

M.
 
Upvote 0
Second question
I4
=INDEX(INDEX(B:B,MATCH($H3,$A:$A,0)):B$1000,MATCH($H4,INDEX($A:$A,MATCH($H3,$A:$A,0)):$A$1000,0))
drag to J4

Then
select I4:J4
copy (Ctrl+C)
select I8
paste (Ctrl+V)

Hope this helps

M.
 
Upvote 0
Solution
A more robust formula
I4
=IF(ISNA(MATCH($H4,INDIRECT(SUBSTITUTE($H3," ","_")),0)),"",INDEX(INDEX(B:B,MATCH($H3,$A:$A,0)):B$1000,MATCH($H4,INDEX($A:$A,MATCH($H3,$A:$A,0)):$A$1000,0)))

drag to J4
Select I4:J4
Copy
Select I8
Paste

M.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top