Need an Idea

[monagan]

Whether in VB or Not, I have been trying for days to figure this one out.
I am not looking for an answer or solution, just a point in the right direction.

I have 2 combo boxes, State, City
And a data validation list,wind speed

So here it goes...

When the state is chosen, a list of cities come up.
choose the city, and then choose wind speed.
Lo,Avg,Hi

From a 2 inch stack of papers for a lot of cities in the US I have figured out the Lo,Avg,and Hi.

Is there a neat way I have not figured out yet to easily have all of this data fill three cells?
_________________________________________________________

Choose State: |__________
Choose: City: |__________
Choose Wind Speed: |___________ Hi,avg,lo

 

[ETID]

How is the raw data table laid out?

 

[monagan]

I'm not sure what you mean by raw table.

 

[ETID]

I assume that your pulling this data into lists from some type of electronic source.

What is it and how is it arranged?

 

[monagan]

I have all my data on an ecel spread sheet.

I have a list of 322 cities with dynamic named ranges
and 50 states

Cities States
houston Alaska
NYC Alabama
.... .........
.... ........

Then I have a table of hi's and lows and average speeds
5
7
10
------
6
8
19
------
8
10
12
------
and so on

then I have three cells that will change at the top as city ans state change
Hi:
AVG:
LOW:

 

[ETID]

Ok...in sheet2 i have in cells A1 through B2

STATE CITY
OH ...in this cell is a data validation based on the result of "ActiveWorkbook.Names.Add Name:" function in the code below.(a named range called "returned_city")

and in sheet 1 cells A1 through B11

State City
PA Reading
PA Happy Valley
PA Phila.
PA Pitt.
OH Stubenville
OH Cinncinatti
OH Clevland
CA Oakland
CA San diego
CA Hollywood


then I manually input a State code in SHeet2 A2 and run the code below
(you will probably launch this via an event)
to update the data validation city list in sheet B2



Sub Set_City_list()

Dim x As String
Sheets("sheet1").Range("c:d").ClearContents
Sheets("sheet2").Range("b2").ClearContents

Sheets("sheet1").Range("A1:B11").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("sheet2").Range( _
"A1:B2"), CopyToRange:=Sheets("sheet1").Range("C1"), Unique:=True



x = CStr(Application.WorksheetFunction.CountA(Sheets("sheet1").Range("d:d")))

ActiveWorkbook.Names.Add Name:="Return_City", RefersToR1C1:= _
"=Sheet1!R2C4:R" + x + "C4"

End Sub



...repeat this procedure for your high - lo table

 

[monagan]

I'm sorry, but that confused me.
I've been trying to do excatly what you did to see what it did but It doesn't seem ot be doing anything.

 

[ETID]

have you set sheet2 b1 to the named range using data validation?

 

[ETID]

...be sure to set validation to list and =Return_City in the range

 

[monagan]

i tried but it results in an error

 

[ETID]

Ok ..check list

do you have macros enabled? and

is there a named range called "return_city" in the list of named ranges?

..are you running the macro after you enter a state code in sheet2 A2

did you click sheet2 B2 to see if there is anything in the drop down?