Chookz
Board Regular
- Joined
- May 9, 2011
- Messages
- 95
Hey guys,
I have data that I want to add to 45 reports (each on different worksheets).
Im currently creating the first report (on worksheet 1) and once it's done I plan on copying the sheet 45 times to get my 45 reports. The reports use vlookups, index and match formulas to display the data so I just change the name on each report sheet.
I have some data that I would like to put in a chart. Problem is this data will be a dynamic range. eg. on 1 report it may be a range of 10 rows, on another report it might be 5 rows.
If i make a chart with absolute ranges (eg. I just make it say 12 rows so it will get any range I have in my reports) I can use that when I copy the worksheet 45 times, however when the report has less than 12 rows of data for the chart I get these big spaces on my chart.
Ive used dynamic named ranges with the OFFSET function in charts before, but if I want to copy worksheets then the named range will keep referencing back to my first worksheet. I will have a few charts per report, so with this method i'd be looking at creating 100+ named ranges, then manually entering those ranges in the individual charts. Then it gets hard if i ever need to make a change to one of the charts.
So Im wondering is it possible to create a dynamic named range for the entire workbook that would select a range say between A1:A20 regardless of what sheet you are on? And use this as a chart series reference?
Then I can copy my sheet 45 times for all the reports and it will look to the range in A1:A20 on each new sheet?
Hope that makes sense?
Thanks in advance
I have data that I want to add to 45 reports (each on different worksheets).
Im currently creating the first report (on worksheet 1) and once it's done I plan on copying the sheet 45 times to get my 45 reports. The reports use vlookups, index and match formulas to display the data so I just change the name on each report sheet.
I have some data that I would like to put in a chart. Problem is this data will be a dynamic range. eg. on 1 report it may be a range of 10 rows, on another report it might be 5 rows.
If i make a chart with absolute ranges (eg. I just make it say 12 rows so it will get any range I have in my reports) I can use that when I copy the worksheet 45 times, however when the report has less than 12 rows of data for the chart I get these big spaces on my chart.
Ive used dynamic named ranges with the OFFSET function in charts before, but if I want to copy worksheets then the named range will keep referencing back to my first worksheet. I will have a few charts per report, so with this method i'd be looking at creating 100+ named ranges, then manually entering those ranges in the individual charts. Then it gets hard if i ever need to make a change to one of the charts.
So Im wondering is it possible to create a dynamic named range for the entire workbook that would select a range say between A1:A20 regardless of what sheet you are on? And use this as a chart series reference?
Then I can copy my sheet 45 times for all the reports and it will look to the range in A1:A20 on each new sheet?
Hope that makes sense?
Thanks in advance
