Solved: Drop Down Menu to Display Other Sheets

[JAG836]

Currently I have a large number of different sheets. Each sheet represents a different city, but all of the sheets are formatted the same (data and two charts, with the same rows, columns etc.).


I would like to narrow this down into a handful of sheets that represent different regions of the country, so that I dont have to scroll through all of the sheets to find the one I want. So the plan is for each regional sheet to have a drop down menu, where it would list all of the particular cities in that region. When you select a city, it would display that city's information.

Hopefully that wasn't too confusing. Is there a way to do such a thing? How would I do so if there is?


Thank you so much for any help!

[perhol]

Maybe you can use John Walkenbach’s Menumaker at http://j-walk.com/ss/excel/tips/tip53.htm for that purpose.

I use it to run different macros and to go to the sheet i want.

Also you could take a look at andrewsexceltips at http://www.andrewsexceltips.com/my_tips.htm. He have an Multi Menu Maker and also an New Book Navigator that could do what you want.

[asingh]

Hi,

Which option you want:

1. A drop down menu which lists all the tab names [cities] and when you choose the city you are taken to that tab..?

or

2. You go to your tab [city sheet] and select a drop down menu (for region) which refreshes the graphs and data for the selected region.

...attach a sample if you can...

thanks and regards,

asingh

[JAG836]

Hi,

Which option you want:

1. A drop down menu which lists all the tab names [cities] and when you choose the city you are taken to that tab..?

or

2. You go to your tab [city sheet] and select a drop down menu (for region) which refreshes the graphs and data for the selected region.

...attach a sample if you can...

thanks and regards,

asingh

Option 1. Would it work if I hid the city tabs, so only the region tabs are displayed?

I would prefer that I didnt attach the file, as the info could be sensitive. If you really want it, I could go through and edit it so I can attach it.

[JAG836]

Actually, this may help the understanding of what I am trying to do. This file isn't the actual file I'm working with, but I just made it to give an idea of what I'm trying to accomplish.

Right now I have a tab for every city (Stockton, Adelanto, Redding etc.). These need to be organized by region like is shown in this file. So I would like to make a new tab for each region (West, Northwest etc.), where there is a drop down menu to select a city in that region. Upon selecting the city, the info is then displayed right there in the tab. This way, I don't have a ton of tabs to sort through, I only have the region ones, which then lead me to a city.

I hope that helps!

[JAG836]

anyone?

[perhol]

Coming soon!

[perhol]

Have made an example with my suggestion about using a menu.
The file contains macros, you have to set macrosecurity to medium and accept to open the file with macros.
The menu is to the right of the Help-menu.
Also ther is 2 macros to hide and unhide the tabs.
You find them by holding the alt-key while pressing the f8 key.
Here it is:

[JAG836]

Have made an example with my suggestion about using a menu.
The file contains macros, you have to set macrosecurity to medium and accept to open the file with macros.
The menu is to the right of the Help-menu.
Also ther is 2 macros to hide and unhide the tabs.
You find them by holding the alt-key while pressing the f8 key.
Here it is:

This is great! Thank you so much!

[perhol]

Glad it works for you