Create a chart of a city with neighboring cities

  • I am using a ComboBox to make the user select a City. Based on the City chosen I would like to display neighboring cities in a chart.


    Ex. City1 has 4 neighbors, City 12, City 15, City 19 and City 25.


    I would like to see a chart with City1 , City 12, City 15, City 19 and City 25.


    If I select another City I would like to see that City's neighbours.


    I am thinking about doing a select case statement but I am not sure where to start???

  • Thank you for your reply!


    How would I use the dependent combo boxes in a chart? I was thinking about doing pivottables in some way.


    Or doing an advanced filter with copy to a hidden chart and then do the charts from the hidden sheet.


    All suggestions are welcome!

  • It's unclear to me how cities (city names?) would appear as a "chart." A chart displays numerical comparisons, and "cities" are not that. If you want to show how distant (or how populous, or how large in hectares, etc.) each neighbouring city is, then that could be a chart.

  • Thank you for your reply and good work!


    Would you mind posting the formulas you used in the picture?


    Another question is how can I select City1 and get cities that are close to City1 in population?

  • Cell B14 (with the dropdown list) uses range D4:D6 for its validation list.The adjacent cells (which need not be adjacent) have formulas to lookup from the neighbours range. Cell C14 has
    =VLOOKUP($B$14,$D$4:$F$6,2,FALSE)
    and cell D14 has
    =VLOOKUP($B$14,$D$4:$F$6,3,FALSE)
    The cells below those look up the populations. Cell C15 has
    =VLOOKUP(C$14,$A$3:$B$7,2,FALSE)
    and cell D15 has
    =VLOOKUP(D$14,$A$3:$B$7,2,FALSE)
    The graph uses range C14:D14 of course. One could have its title include the value in C14.


    I should have time later today to create an example of using array formulas to find the cities closest in population. Or perhaps you could work it out with that clue. ;)

  • It is possible to create an Excel array formula to find the city with the closest, next closest, etc. population from a range like I have in A3:B7. It is quite an advanced technique, though, similar to this.

  • It is possible to create an Excel array formula to find the city with the closest, next closest, etc. population from a range like I have in A3:B7. It is quite an advanced technique, though, similar to this.


    Can you give me some hints on how to create the array?

  • Cell A11 has a List validation using range A3:A7.
    Cell A12 has this formula: =VLOOKUP($A$11,$A$3:$B$7,2,FALSE)
    Cell B11 has: =INDEX($A$3:$A$7,MATCH(B$12,$B$3:$B$7,0))
    Cell C11 has: =INDEX($A$3:$A$7,MATCH(C$12,$B$3:$B$7,0)) so it can just be copied from B11
    Cell B12 has: =INDEX($B$3:$B$7,MATCH(SMALL(ABS($B$3:$B$7-$A$12),2),ABS($B$3:$B$7-$A$12),0)) entered as an array formula so it displays as {=INDEX($B$3:$B$7,MATCH(SMALL(ABS($B$3:$B$7-$A$12),2),ABS($B$3:$B$7-$A$12),0))}
    Cell C12 has: =INDEX($B$3:$B$7,MATCH(SMALL(ABS($B$3:$B$7-$A$12),3),ABS($B$3:$B$7-$A$12),0)) entered as an array formula
    Cell A14, used as the chart title, has: ="Cities Closest in Population to "&$A$11
    The chart uses range A11:C12. To extend the chart to show more cities, copy C11:C12 to the right and then edit the copied formulas in row 12 to increment the number used with the SMALL function. Save as arrays after editing.


    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"CitiesPop.JPG","data-attachmentid":1207430}[/ATTACH]

  • Thank you for your reply and for your example. I am going to try this at once and it looks like an interesting use of formulas!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!