PDA

View Full Version : Solved: Drop Down Menu to Display Other Sheets



JAG836
02-06-2008, 02:56 PM
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.:think: 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
02-06-2008, 04:49 PM
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
02-06-2008, 05:00 PM
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
02-07-2008, 06:32 AM
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
02-07-2008, 06:46 AM
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! :dunno

JAG836
02-07-2008, 11:41 AM
anyone?

perhol
02-07-2008, 12:14 PM
Coming soon!

perhol
02-07-2008, 01:23 PM
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
02-07-2008, 02:03 PM
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! :clap: Thank you so much! :bow:

perhol
02-07-2008, 04:45 PM
Glad it works for you :thumb