How to auto populate many cells based on one cell

B

Banshee

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?
 
M

Max

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
 
B

Banshee

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.
 
B

Banshee

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto-populate other cells based on dates 2
How to auto-populate worksheet based on cell entry? 4
Auto populate a cell based off a look up table 2
Auto Populate Fields based on Drop Down List Selection 2
Auto Populate Based on Other Selection 2
Populating other cells based on a choice from a drop-down list 1
How to Auto-populate cell based on selection of a list item 2
How to filter rows data based on background color of individual cells 3

Top