Re: How to auto populate many cells based on one cell

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Relevant Pages

  • Re: comparing distinct data
    ... you can use VLOOKUP to bring the City and State information from Sheet 2 to ... assume on sheet1 your IDs are in column A and you want City in D and State ... > In another worksheet, I also have a column full of unique id numbers. ...
    (microsoft.public.excel.programming)
  • RE: Reflecting changes on multiple sheets
    ... I've redone complete workbooks that were originally done like this because ... formulas to the "Vancouver" sheet in order to get everything to show up. ... 4-If you add a new city you must then add a new sheet along with all of the ... If VANCOUVER is found to be the city, then place it in the VANCOUVER sheet, ...
    (microsoft.public.excel.programming)
  • PivotTables with Multiple Fields
    ... sheet, so that to find the state associated with the city, it'd be: ... I created a named range for each state. ... .InCellDropdown = True ...
    (microsoft.public.mac.office.excel)
  • RE: Hyperlink Questions
    ... the sheet for the city: ... macro creates a new worksheet for each city. ... will take me directly to the sheet which was created for the city ... New York New York ...
    (microsoft.public.excel.misc)
  • Re: entering data from a list
    ... and display next to city like below: ... in sheet2 you will have code, city & reason listing from range b4:d22 ... in sheet2 you will have reason 01.. ... Sheet 1 ...
    (microsoft.public.excel.worksheet.functions)