Dynamic Graphs

[MROMAR]

What I'm attempting to do is through the use of a drop down list have my four graphs automatically change depending on which option is selected from the drop down list.

My data contains a Region: WEST,ONT, QUE, NFLD, MAR, each region has cost drivers, I've selected four that I want to graph. Admin Hours, Dock Hours, Hired Truck Cost, Agency Cost. All of these are dollar costs.

My data source is quite large nearly 5000 entries, with entries spanning all 12 months. So I

I need to produce four graphs one that would graph the monthly cost of each of these drivers, but I need to be able to change their regions with the click of a button so I can see all four graphs for one region at a time. So the drop down list would list the Regions and the four graphs change depending on which region is selected.

The graphs and the data would be in separate tabs.

Does anyone know how to do this? I'm sure it's possible but I'm inexperienced with Excel and any help would be great.

[jeffreybrown]

See if this example which uses dynamic named ranges (Ctrl + F3) helps...

[MROMAR]

Nvm jeffreybrown, it works.

Can you explain how you did it?

[MROMAR]

Doesn't seem to be working for me when I open the file up, I select diffrent regions and nothing happens.

[jeffreybrown]

• Create data elements in A1:B16
• In D2 created data validation of off unique list in M2:M5
• Create bar chart from A1:B16

---------------------------------------------------------------------------------------------------------------------------

• Created two named ranges (Ctrl + F3)
• =OFFSET(reference,rows,cols,height,width)
• reference >> Sheet1!$A$1
• rows >> MATCH(Sheet1!$D$2,Sheet1!$A$2:$A$16,0) >> Tells the Offset function where D2 begins
• cols >> 0 >> Move over 0 cols, but for MyRange change to a 1 which moves over 1 col to col B
• height >> COUNTIF(Sheet1!$A$2:$A$16,Sheet1!$D$2) >> Tells the Offset function how many times the value in D2 is found
• width >> nothing (leave blank)

---------------------------------------------------------------------------------------------------------------------------

• When put together they look like:

Name >> MyAxis >> Refers to: >> =OFFSET(Sheet1!$A$1,MATCH(Sheet1!$D$2,Sheet1!$A$2:$A$16,0),0,COUNTIF(Sheet1!$A$2:$A$16,Sheet1!$D$2))
Name >> MyRange >> Refers to: >> =OFFSET(Sheet1!$A$1,MATCH(Sheet1!$D$2,Sheet1!$A$2:$A$16,0),1,COUNTIF(Sheet1!$A$2:$A$16,Sheet1!$D$2))

---------------------------------------------------------------------------------------------------------------------------

• Right click on chart >> select data
• Edit Legend Entries (Series) >> Replace series reference of B2:B16 with MyRange
• Edit Horizontal (Category) Axis Label >> Replace series reference of A2:A16 with MyAxis

Hope this helps and I didn't leave anything out

If you run into some hiccups, there are many good tutorials on the web. Some examples...

http://www.tushar-mehta.com/excel/ne...ynamic_charts/
http://www.andypope.info/charts.htm

Other useful links...
http://www.excelforum.com/excel-form...additions.html
http://www.excelforum.com/excel-prog...materials.html

...and many others

[MROMAR]

Lets say we have data looking like this

Region Cost Driver Month Cost
East 1 JAN 21
East 2 FEB 15
East 3 MAR 14
East 4 APR 97
West 1 MAY 77
West 2 JUN 62
West 3 JUL 34
West 4 AUG 69
South 1 SEP 66
South 2 OCT 99
South 3 NOV 52
North 4 DEC 21
North 1 JAN 9
North 2 FEB 72
North 3 MAR 52

If need 4 different graphs for 4 diffrent cost drivers that are depended on a Region being selected from a drop down menu. If I select a region all four graphs would need to change. They graph the cost per month for that Cost driver.

Any ideas?

EDIT I don't know why the table looks so off, it looks nomal before I hit reply. Anyways, the 1,2,3,4 are Cost Drivers, the months are self-explanatory and the numbers on the furtherst left are the costs.

[MROMAR]

Can anyone assist me with this?

[jeffreybrown]

I'm sorry, I thought for sure I had left a message here.

Please attach a file with a good description of what you desire.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[MROMAR]

Attachment 184020So I've uploaded the same file you sent me, but I've made changes to reflect what I need. To upload the actual file that I'm using is impossible because the data is too large.

So basically from the file I uploaded I need four diffrent graphs for each of teh 4 drivers. The four graphs would need to update when I select a region, they would sum all of the cost for each month.

[jeffreybrown]

I'm sorry but I guess just don't see your needs here. If you select a region, (East, West, etc.) with this data it doesn't make sense having four charts.

If you select the South region there are only three data points so I am missing the big picture I guess.

[MROMAR]

Well there is a lot more data, in the 5000-6000 range.

The graphs are for each of the cost drivers, instead of 1, 2, 3, 4, let's call them Admin Hours, Dock Hours, Hired Truck Cost, Agency Cost. I want to graph each of these cost drivers based on a Region.
So if I select West, I can graphically see how cost went up or down over the last year in Dock Hours, or in Hired Truck.

I want to be able to notice by a glance if Agency Cost went up between Jan and Feb without having to look at the data.