If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
One Chart, Same Data ranges, different source sheets
Hi All!
I have a sheet with numerous charts. The charts are standard, much like a dashboard. The charts are fed from one sheet which is a rollup of summary data from many other sheets. I need to create a similar summary sheet for each of the remaining regions, but only need one sheet with charts. Is there a way to make the SHEET reference for the charts dynamic - say, populated by the user selecting an entry from a drop menu? The desired behavior is as follows: The Chart Sheet show 10 charts reflecting various data for Region A. I want to be able to change all the charts to reflect the data from Region B, C, D, E, etc., in lieu of having a chart sheet for each region (thus creating a larger workbook). The Region sheets are all exactly the same, the data ranges are the same for all Region sheets, the chart types do not need to change. The only change in the Charts is the sheet name for the source data. I have tried named ranges, indirects, both of those together, Cell references. I have found threads on changing data ranges, X/Y axis settings, etc., but nothing on feed one set of charts by selecting the name of the source sheet, thus changing the entire set of charts. All help is appreciated! Thanks! -- Greg |
#2
|
|||
|
|||
One Chart, Same Data ranges, different source sheets
Hi Greg in CO,
The ADDRESS function will accept a sheet name from a cell reference =ADDRESS(rowNo,columnNo,1,0,SheetName) Combine this with the INDIRECT function to get the value of the cell with that address. There is a sample showing how to build charts using this method at http://edferrero.com/ExcelCharts/tabid/102/Default.aspx Look for the 'Reporting' sample. Ed Ferrero www.edferrero.com |
#3
|
|||
|
|||
One Chart, Same Data ranges, different source sheets
-- Greg "Ed Ferrero" wrote: Hi Greg in CO, The ADDRESS function will accept a sheet name from a cell reference =ADDRESS(rowNo,columnNo,1,0,SheetName) Combine this with the INDIRECT function to get the value of the cell with that address. There is a sample showing how to build charts using this method at http://edferrero.com/ExcelCharts/tabid/102/Default.aspx Look for the 'Reporting' sample. Ed Ferrero www.edferrero.com . Hi Ed! Thanks for the info...those examples are great....however, not quite where I was going. What I am trying to do, in a nutshell, is modify this example: I have a set of Summary sheets named RegionA, RegionB, RegionC, etc. Each of these sheets is exactly the same in layout, except for the Sheet name. Each of these sheets is fed by subordinate sheets, but the subordinate sheets do not affect the charts. I have a sheet of charts, fed by a summary sheet. Each Summary sheet needs the same set of charts. Rather than create a set of charts for each Summary sheet, I would like to do the following, if possible: Charts! - the name of the Chart sheet RegionA - the name of the Summary sheet for RegionA Sample series data from one of the Charts on the Charts sheet: Series Name: ="RegionA Wigets" (It's hard coded for the moment...ideally I would link this to a dynamic cell or make it generic) Series: =RegionA!$K$192:$AD$192 Desired behavior: Using a drop menu selection, the Series entry for "RegionA!" would change to whatever was selected, thereby changing the entire set of charts on the Charts sheet. So, after selecting the Drop Menu item "RegionB", the Series would now read: =RegionB!$K$192:$AD$192 This would apply to all the charts on the Charts page, allowing the user to switch between regions without having to select other tabs or have the associated workbook size for one with multiple chart sheets. Ed, if your suggestion would make the change noted above, my apologies - I didn't see how it worked in your example. Could you elaborate? Thanks for your guidance and patience! |
#4
|
|||
|
|||
One Chart, Same Data ranges, different source sheets
Hi Ed! I have been trying to modify the formulas from your example
spreadsheet, using INDIRECT and ADDRESS - no luck. I have not used address before - I can get the formula to return a cell value (the Cell address where the drop menu populates the sheet names from the drop menu), but I am not sure how to integrate this with an INDIRECT argument. Would that go into a Named Range formula? Would it go into the SERIES formula? I've tried various options - no luck. I am baffled that trying to change the Sheet! reference in a chart has turned out to be so complicated. I know there are 3rd party add-ins or VB options in a macro - but I am trying to keep it simple for the end users to maintain. Thanks! -- Greg "Greg in CO" wrote: -- Greg "Ed Ferrero" wrote: Hi Greg in CO, The ADDRESS function will accept a sheet name from a cell reference =ADDRESS(rowNo,columnNo,1,0,SheetName) Combine this with the INDIRECT function to get the value of the cell with that address. There is a sample showing how to build charts using this method at http://edferrero.com/ExcelCharts/tabid/102/Default.aspx Look for the 'Reporting' sample. Ed Ferrero www.edferrero.com . Hi Ed! Thanks for the info...those examples are great....however, not quite where I was going. What I am trying to do, in a nutshell, is modify this example: I have a set of Summary sheets named RegionA, RegionB, RegionC, etc. Each of these sheets is exactly the same in layout, except for the Sheet name. Each of these sheets is fed by subordinate sheets, but the subordinate sheets do not affect the charts. I have a sheet of charts, fed by a summary sheet. Each Summary sheet needs the same set of charts. Rather than create a set of charts for each Summary sheet, I would like to do the following, if possible: Charts! - the name of the Chart sheet RegionA - the name of the Summary sheet for RegionA Sample series data from one of the Charts on the Charts sheet: Series Name: ="RegionA Wigets" (It's hard coded for the moment...ideally I would link this to a dynamic cell or make it generic) Series: =RegionA!$K$192:$AD$192 Desired behavior: Using a drop menu selection, the Series entry for "RegionA!" would change to whatever was selected, thereby changing the entire set of charts on the Charts sheet. So, after selecting the Drop Menu item "RegionB", the Series would now read: =RegionB!$K$192:$AD$192 This would apply to all the charts on the Charts page, allowing the user to switch between regions without having to select other tabs or have the associated workbook size for one with multiple chart sheets. Ed, if your suggestion would make the change noted above, my apologies - I didn't see how it worked in your example. Could you elaborate? Thanks for your guidance and patience! |
#5
|
|||
|
|||
One Chart, Same Data ranges, different source sheets
Don't know whether this is what you already tried, but my approach would be:
Create a drop down on the chart sheet (dashboard), possibly using a compbo box but knowing me more likely using data validation. Either list the sheets directly, or in another hidden cell do a lookup to a table to map the selection to s aheet name (eg "USA & Canada" might map to a sheet just called "NorthAmerica") Create named ranges which are local to your dashboard sheet (not workbook scope) using offset and indirect, the indirect using the selected sheet name. You need to do this for every series of every [dynamic] chart. So eg a range for Chart1_SalesSeries which uses OFFSET to select the range on a sheet specified by INDIRECT plus some concatenated stuff for the start cell reference. Your indirect would look something like: INDIRECT("'"&$M$22&"'!$C$7") Where M22 on the current sheet has the name of the source data summary sheet (and is probably better as a named range itself), and C7 is the starting cell for the summary data range on that source sheet, from which you will OFFSET by an appropriate amount for each series of data. Note the single quote marks to wrap round any names with spaces and other awkward characters. For each series, replace the bits in the formula bar for the data values with you new named range. Wash, rinse, repeat. Tedious, but once built this will be pretty solid. Alternative - use a single summary sheet to pull data into a single table for all regions in a "normalised" layout rather than report style. Use PivotCharts instead of normal charts, filter for regions (also added bonus of being able to show totals, individuals entires, multiple entries together etc. Hope this helps Adam On 04/03/2010 22:30, Greg in CO wrote: Hi All! I have a sheet with numerous charts. The charts are standard, much like a dashboard. The charts are fed from one sheet which is a rollup of summary data from many other sheets. I need to create a similar summary sheet for each of the remaining regions, but only need one sheet with charts. Is there a way to make the SHEET reference for the charts dynamic - say, populated by the user selecting an entry from a drop menu? The desired behavior is as follows: The Chart Sheet show 10 charts reflecting various data for Region A. I want to be able to change all the charts to reflect the data from Region B, C, D, E, etc., in lieu of having a chart sheet for each region (thus creating a larger workbook). The Region sheets are all exactly the same, the data ranges are the same for all Region sheets, the chart types do not need to change. The only change in the Charts is the sheet name for the source data. I have tried named ranges, indirects, both of those together, Cell references. I have found threads on changing data ranges, X/Y axis settings, etc., but nothing on feed one set of charts by selecting the name of the source sheet, thus changing the entire set of charts. All help is appreciated! Thanks! |
Thread Tools | |
Display Modes | |
|
|