I have a workbook with 3 sheets. In sheet "COVER" there are 2 cells,
A1 & A2. In cell A1 there is a drop down list that has the names of
states...In cell A2 there is a drop down list that has the names of
the cities in those states. Its set up where the list in A2 only shows
the cities in the state selected in A1.

In sheet "LOCATION" there is a list of states, in column A, a list of
cities within those states in column B, and a number associated with
each city in column C.

In sheet "PRODUCT" i need it to have the ability so that when a
certain state & city are selected in sheet "COVER" that the assicated
number with that city (listed in sheet "LOCATION") is copied into cell
A1 in sheet "PRODUCT". And if a state is selected and not a city, in
sheet "COVER" that an average of all the number within that particular
state is copied into cell A1 in sheet "PRODUCT".

I can get it to work with the states but when i try to factor in the
cities i'm having trouble. My code so far is:

=IF(COVER!B16="Alabama",LOCATIONSTATE!C2,IF(COVER!
B16="ALASKA",LOCATIONSTATE!C3,0)) etc.....

An example of how the information in the "LOCATION" sheet looks:

Alabama
Birmingham 1.01
Tuscalossa .85

Alaska
Anchorage .5
Fairbanks .75

Anyone have an idea of how i can get this to work?