Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Use Scroll Bar to Change Source Data for xlColumnClustered SheetCh
I have a SheetChart that has two vertical columns for each of the 17
departments that we have at our company along the X-Axis and Hours along the Y-Axis. My SourceData is in Sheets("Summary"), Col. C represents Available Hours and Col.D represents Scheduled Hours. I want to have the ability to graph each of the 5 Weeks on the same SheetChart. What I would like to do is embed a ScrollBar that would change the SourceData from Week 1 -- Week 2, Week 2 -- Week 3, ... , Week 4 -- Future, and visa versa. I think the best way to setup the code is to change the SourceData row numbers as the ScrollBar Value changes. For example, ScrollBar.Value = 1 and Week 1 SourceData = Sheets("Summary").Range("C10:D10,C18:D18,C26:D26, etc."), ScrollBar.Value = 2 and Week 2 SourceData = Sheets("Summary").Range ("C11:D11,C19:D19,C27:D27, etc."), and so on. The ScrollBar Value Min. = 1 and Max. = 5. Col. C Col.D Department 1 of 17: Available Hrs Scheduled Hrs Week 1 40 40 Week 2 80 50 Week 3 40 30 Week 4 0 0 Future 120 15 Department 2 of 17: Available Hrs Scheduled Hrs Week 1 40 40 Week 2 80 50 Week 3 40 30 Week 4 0 0 Future 120 15 And so on.... Note: I have Excel 2003. Hope this explains everything! Thanks in Advance! -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Use Scroll Bar to Change Source Data for xlColumnClustered SheetCh
Hi,
The simplest thing is to build a table of values to be charted. Add a scroll bar and set Linked cell to F7. Min value = 0. Max value =4 In G9:H9 enter the series name text, Available Hrs and Scheduled Hrs In F10:F25 enter category labels Department 1 thru to Department 17. In G10 enter the following formula and copy down and across range G10:H26 =OFFSET($A$1,(((ROW()-ROW($G$10))*8)+ROW($G$10)+($F$7-1)),1) Create a chart on the range F9:H26. In E10 enter the formula =OFFSET($A$1,(((ROW()-ROW($G$10))*8)+ROW($G$10)+($F$7-1)),0) Add a chart title and then link the chart title to the cell E10. http://www.andypope.info/tips/tip001.htm The scrollbar will now move thru Week 1 to Furture. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "RyanH" wrote in message ... I have a SheetChart that has two vertical columns for each of the 17 departments that we have at our company along the X-Axis and Hours along the Y-Axis. My SourceData is in Sheets("Summary"), Col. C represents Available Hours and Col.D represents Scheduled Hours. I want to have the ability to graph each of the 5 Weeks on the same SheetChart. What I would like to do is embed a ScrollBar that would change the SourceData from Week 1 -- Week 2, Week 2 -- Week 3, ... , Week 4 -- Future, and visa versa. I think the best way to setup the code is to change the SourceData row numbers as the ScrollBar Value changes. For example, ScrollBar.Value = 1 and Week 1 SourceData = Sheets("Summary").Range("C10:D10,C18:D18,C26:D26, etc."), ScrollBar.Value = 2 and Week 2 SourceData = Sheets("Summary").Range ("C11:D11,C19:D19,C27:D27, etc."), and so on. The ScrollBar Value Min. = 1 and Max. = 5. Col. C Col.D Department 1 of 17: Available Hrs Scheduled Hrs Week 1 40 40 Week 2 80 50 Week 3 40 30 Week 4 0 0 Future 120 15 Department 2 of 17: Available Hrs Scheduled Hrs Week 1 40 40 Week 2 80 50 Week 3 40 30 Week 4 0 0 Future 120 15 And so on.... Note: I have Excel 2003. Hope this explains everything! Thanks in Advance! -- Cheers, Ryan |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Use Scroll Bar to Change Source Data for xlColumnClustered She
I had to alter the formulas to fit my application a bit, but you are dead on.
Your the man. Thanks for the replay! I like the website too, I'll be saving that one for future reference. -- Cheers, Ryan "Andy Pope" wrote: Hi, The simplest thing is to build a table of values to be charted. Add a scroll bar and set Linked cell to F7. Min value = 0. Max value =4 In G9:H9 enter the series name text, Available Hrs and Scheduled Hrs In F10:F25 enter category labels Department 1 thru to Department 17. In G10 enter the following formula and copy down and across range G10:H26 =OFFSET($A$1,(((ROW()-ROW($G$10))*8)+ROW($G$10)+($F$7-1)),1) Create a chart on the range F9:H26. In E10 enter the formula =OFFSET($A$1,(((ROW()-ROW($G$10))*8)+ROW($G$10)+($F$7-1)),0) Add a chart title and then link the chart title to the cell E10. http://www.andypope.info/tips/tip001.htm The scrollbar will now move thru Week 1 to Furture. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "RyanH" wrote in message ... I have a SheetChart that has two vertical columns for each of the 17 departments that we have at our company along the X-Axis and Hours along the Y-Axis. My SourceData is in Sheets("Summary"), Col. C represents Available Hours and Col.D represents Scheduled Hours. I want to have the ability to graph each of the 5 Weeks on the same SheetChart. What I would like to do is embed a ScrollBar that would change the SourceData from Week 1 -- Week 2, Week 2 -- Week 3, ... , Week 4 -- Future, and visa versa. I think the best way to setup the code is to change the SourceData row numbers as the ScrollBar Value changes. For example, ScrollBar.Value = 1 and Week 1 SourceData = Sheets("Summary").Range("C10:D10,C18:D18,C26:D26, etc."), ScrollBar.Value = 2 and Week 2 SourceData = Sheets("Summary").Range ("C11:D11,C19:D19,C27:D27, etc."), and so on. The ScrollBar Value Min. = 1 and Max. = 5. Col. C Col.D Department 1 of 17: Available Hrs Scheduled Hrs Week 1 40 40 Week 2 80 50 Week 3 40 30 Week 4 0 0 Future 120 15 Department 2 of 17: Available Hrs Scheduled Hrs Week 1 40 40 Week 2 80 50 Week 3 40 30 Week 4 0 0 Future 120 15 And so on.... Note: I have Excel 2003. Hope this explains everything! Thanks in Advance! -- Cheers, Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Source Data in Chart | Charts and Charting in Excel | |||
how do i change the data source | Excel Discussion (Misc queries) | |||
Is there a way to keep the formulas and change their source data? | Excel Discussion (Misc queries) | |||
change odbc data source | Excel Worksheet Functions | |||
change data source for many queries at once? | Excel Worksheet Functions |