How do I do this in excel?

[z0mb13]

OK I am looking at a report now. The report shows the production volume per branch (in graph form). The cool thing is that there is a tab for each month. So if I click january, all of the graphs will change and show the january data.

How do I do this? Is this done using macros? How do I look at the source code (or how the person did this) for the report that I am looking at (if it is possible)

Thanks.

 

[ssanches]

You could do this using pivot reports. Search the net for some good documentation on using pivot tables and pivot reports....Alternatively MS Excel's help is quite useful, especially if you're using Excel 2003

 

[PowerEngineer]

I suspect that pivot tables might be the right way to do this, but I've never spent the time to learn about them...

Another way to do something like this is to assemble all the data into a large array (say with the branches as rows and the months as columns). Then set up a special column where each entry uses the INDEX formula and an index set by you so that the special column ends up with the monthly values you want from the array. Plot the special column (against the branches column). Now the month that will appear on the plot will change as you change the index.

Of course, you can do all sorts of things to calculate the index (like basing it on an entered date, etc.)

 

[NeoV]

to look at the source code, use Alt-F11, or tools-macro-vb editor

I'd have to see it to say for sure - is each tab a different worksheet?

Power, you are using the "index" command and you don't know how to use pivot tables? Shame on you!

 

[z0mb13]

sorry..., just to make it clearer... what I mean by tab is not the tab at the bottom. Instead its like a pull down menu for the months.

 

[Hector13]

Originally posted by: z0mb13
sorry..., just to make it clearer... what I mean by tab is not the tab at the bottom. Instead its like a pull down menu for the months.

that sounds more like a pivot table. If you right click somewhere on the data, do you see a "refresh" (or other pivot table functions) listed?

 

[z0mb13]

Originally posted by: Hector13

Originally posted by: z0mb13
sorry..., just to make it clearer... what I mean by tab is not the tab at the bottom. Instead its like a pull down menu for the months.

that sounds more like a pivot table. If you right click somewhere on the data, do you see a "refresh" (or other pivot table functions) listed?

no, not at all..

I Know about pivot tables (I use it a lot). What I am talking about is when you fill in forms, like when is your month of birth, then usually they will have a pulldown menu for the months.

And I think there is actually no macro at all, the graphs are kinda anchored on the month, so if the month changes, all of them changes. I am not sure about this, will check it out more tomorrow when I am at the office

 

[Transition]

I did something which i believe is similar to what your referencing..

I do monthly sales reports for our salesman and developed an Excel worksheet that has all the statistics imaginable. In the very top of my worksheet is a drop down menu which you can select from different salesman. Everything is based upon VLOOKUP's and references back to the cell w/ the drop down.

Sales Report Screenshot (had to remove some information for privacy)

Here's an example of a formula i use to pull the statistics...

=IF((INDIRECT("[stats.xls]"&name&"!"&"B41"))="","",(INDIRECT("[stats.xls]"&name&"!"&"B41")))

So whenever i change the name in my drop down menu, all the stats including my graph change to the selected person. Let me know if you need a better explanation on how this works.

 

[z0mb13]

Originally posted by: Transition
I did something which i believe is similar to what your referencing..

I do monthly sales reports for our salesman and developed an Excel worksheet that has all the statistics imaginable. In the very top of my worksheet is a drop down menu which you can select from different salesman. Everything is based upon VLOOKUP's and references back ot the cell w/ the drop down.

Sales Report Screenshot (had to remove some information for privacy)

Here's an example of a formula i use to pull the statistics...

=IF((INDIRECT("[stats.xls]"&name&"!"&"B41"))="","",(INDIRECT("[stats.xls]"&name&"!"&"B41")))

So whenever i change the name in my drop down menu, all the stats including my graph change to the selected person. Let me know if you need a better explanation on how this works.

how do you add the drop down menu??

 

[Transition]

I'm using Excel 2000...

View > Toolbars > Control Toolbox > Combo Box

 

[NeoV]

a better way to do a drop down menu is to use data validation, and select "list"

would work either way though

 

[Transition]

Ah NeoV... The Excel master himself!

 

[z0mb13]

Originally posted by: NeoV
a better way to do a drop down menu is to use data validation, and select "list"

would work either way though

If I do it this way, the button for the dragdown will not show. Is there a way to make it show?

 

[z0mb13]

Originally posted by: Transition
I'm using Excel 2000...

View > Toolbars > Control Toolbox > Combo Box

hmmm how do I populate the combo box??

 

[Transition]

Originally posted by: NeoV
a better way to do a drop down menu is to use data validation, and select "list"

would work either way though

Hmm i lied - I did my dropdown with Data Validation actually - been a while since i've really examined the report. To insert a data validation drop down you'll want to....

Data > Validation

Settings Tab
Allow: List
Source: I used a seperate worksheet (as you can see pictured in my image) called 'User Lookup'. It contains a list of all my users that the drop-down references.

 

[Transition]

Any luck with this z0mb13?