I have a sheet that lists all 50 states in Column A and several different
ciities in Row 1. In all the corresponding cells it is stated whether the
listed city is 12, 24 or 36 hours away from a particular state.

My question is this: on another worksheet I want to be able to select one of
the cities from a drop down which will auto populate those states that fall
within 12, 24 or 36 hours. Make sense?

Here's a sample formulas play which will deliver the required functionalities
The source ("cross-tab") table below is assumed in sheet: x, in A1:D10

City1 City2 City3
State1 36 24 12
State2 24 24 12
State3 36 24 36
State4 24 12 24
State5 12 24 36
State6 24 24 36
State7 24 12 24
State8 24 24 12
State9 12 36 24

Then in another sheet,
In A1 is a DV droplist to select the city, eg: City2
In E1:G1 are the 3 "hours" col headers, ie: 12, 24, 36

In B2:
=IF(OFFSET(x!$A$1,ROWS($1:1),MATCH($A$1,x!$B$1:$D$ 1,0))=E$1,ROW(),"")
Copy B2 to D2, fill down to D10. Minimize/hide cols B to D.

In E2:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(x!$A:$A,SMALL(B :B,ROWS($1:1))))
Copy E2 to G2, fill down to G10 to populate. All the states for the city
selected in A1 will display within E2:G10, grouped under the corresponding
"hours" header & neatly packed at the top, viz it'll display for example as:

City2 12 24 36
State4 State1 State9
State7 State2
State3
State5
State6
State8


voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Banshee" wrote:
I have a sheet that lists all 50 states in Column A and several different
ciities in Row 1. In all the corresponding cells it is stated whether the
listed city is 12, 24 or 36 hours away from a particular state.

My question is this: on another worksheet I want to be able to select one of
the cities from a drop down which will auto populate those states that fall
within 12, 24 or 36 hours. Make sense?

Max,
Thank you, that seems to work great. I only have one problem now. I have set
it up just as you described only I listed all fifty states plus Washington
DC, so my rows go down to 52. Additionally I have 10 cities listed across the
top row. I have made some edits to the formulas to account for this however
when I select a city from the drop down only the 12 hour column fills in, the
24 and 36 fill in with #REF!. Here are the formulas I used:

In cell B2 =IF(OFFSET('Scenarios (2)'!$A$1,ROWS($1:1),MATCH($A$1,'Scenarios
(2)'!$B$1:$K$1,0))=L$1,ROW(),"")

In cell L2 =IF(ROWS($1:1)COUNT(B:B),"",INDEX('Scenarios
(2)'!$A:A,SMALL(B:B,ROWS($1:1))))

If you can help me out with this I should have it. Thanks again.
"Max" wrote:

Here's a sample formulas play which will deliver the required functionalities
The source ("cross-tab") table below is assumed in sheet: x, in A1:D10

City1 City2 City3
State1 36 24 12
State2 24 24 12
State3 36 24 36
State4 24 12 24
State5 12 24 36
State6 24 24 36
State7 24 12 24
State8 24 24 12
State9 12 36 24

Then in another sheet,
In A1 is a DV droplist to select the city, eg: City2
In E1:G1 are the 3 "hours" col headers, ie: 12, 24, 36

In B2:
=IF(OFFSET(x!$A$1,ROWS($1:1),MATCH($A$1,x!$B$1:$D$ 1,0))=E$1,ROW(),"")
Copy B2 to D2, fill down to D10. Minimize/hide cols B to D.

In E2:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(x!$A:$A,SMALL(B :B,ROWS($1:1))))
Copy E2 to G2, fill down to G10 to populate. All the states for the city
selected in A1 will display within E2:G10, grouped under the corresponding
"hours" header & neatly packed at the top, viz it'll display for example as:

City2 12 24 36
State4 State1 State9
State7 State2
State3
State5
State6
State8


voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Banshee" wrote:
I have a sheet that lists all 50 states in Column A and several different
ciities in Row 1. In all the corresponding cells it is stated whether the
listed city is 12, 24 or 36 hours away from a particular state.

My question is this: on another worksheet I want to be able to select one of
the cities from a drop down which will auto populate those states that fall
within 12, 24 or 36 hours. Make sense?

Nevermind about my last question. I've got it. This is exaclty what I was
looking for, thanks a lot!

"Max" wrote:

Here's a sample formulas play which will deliver the required functionalities
The source ("cross-tab") table below is assumed in sheet: x, in A1:D10

City1 City2 City3
State1 36 24 12
State2 24 24 12
State3 36 24 36
State4 24 12 24
State5 12 24 36
State6 24 24 36
State7 24 12 24
State8 24 24 12
State9 12 36 24

Then in another sheet,
In A1 is a DV droplist to select the city, eg: City2
In E1:G1 are the 3 "hours" col headers, ie: 12, 24, 36

In B2:
=IF(OFFSET(x!$A$1,ROWS($1:1),MATCH($A$1,x!$B$1:$D$ 1,0))=E$1,ROW(),"")
Copy B2 to D2, fill down to D10. Minimize/hide cols B to D.

In E2:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(x!$A:$A,SMALL(B :B,ROWS($1:1))))
Copy E2 to G2, fill down to G10 to populate. All the states for the city
selected in A1 will display within E2:G10, grouped under the corresponding
"hours" header & neatly packed at the top, viz it'll display for example as:

City2 12 24 36
State4 State1 State9
State7 State2
State3
State5
State6
State8


voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Banshee" wrote:
I have a sheet that lists all 50 states in Column A and several different
ciities in Row 1. In all the corresponding cells it is stated whether the
listed city is 12, 24 or 36 hours away from a particular state.

My question is this: on another worksheet I want to be able to select one of
the cities from a drop down which will auto populate those states that fall
within 12, 24 or 36 hours. Make sense?

Welcome, glad to hear
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Banshee" wrote in message
...
Nevermind about my last question. I've got it. This is exaclty what I was
looking for, thanks a lot!