Workbook design help

Xenoun

Board Regular
Joined
Feb 15, 2011
Messages
63
Hello,

I have recently completed a workbook that trends a fair amount of data for a specific group. In total I have 13 of these groups with data that needs to be trended, and I plan to use the first one as a template.

Ideally I would like to be able to click a drop down menu, have it display all the groups and then when I click on the desired group it will bring up the trend graphs.

Does anyone know what the best method would be to achieve this? My first trend workbook uses four sheets, and requires a button to be pushed that updates data that it grabs from the master workbook. The button also updates a few formatting fixes and a pivot table.

Any suggestions would be helpful, i'm not really sure how to go about doing this due to the nature of the spreadsheet. I'll also have to look into how to construct a drop down menu
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,982
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
There are a number of options.
The simplest is probably to preapre al the graph sheet permutations. Then hide them all. When selecting a certain group, only the valid sheet(s) are then unhidden and available.

This is simple to achieve with
Code:
sheets("yoursheet").visible = false  ' hide
'or
sheets("yoursheet").visible = true  ' make visible

First create your set of macros to hide/show the relevant sheets

then somewher in your spreadsheet (on a hidden sheet) have a table with the 13 group names. Name the list

Then from the Forms controls select the combobox paint it on your home sheet somewhere and link the form to your selection macro
 

Xenoun

Board Regular
Joined
Feb 15, 2011
Messages
63
Thanks for the reply. From what you've said it seems like I should dump the 4 sheets from each group into one workbook and just have the sheet with the graph appear and update when the person selects that group

Can you confirm whether i've understood that correctly or if you're meaning something else?
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,982
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Yep, that is what I meant.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,610
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top