Copy and Pasting Charts down 1 excel sheet

[WarpathMechanic]

I thought this would be simple but due to excel's constant usage of absolute cell referencing it is causing me issues.


Q1 Q2 Q3 Q4 Total

2011 $69,307 $104,317 $80,642 $35,109 $289,375

2010 $174,325 $138,097 $281,717 $173,726 $767,865

2009 $114,269 $114,143 $75,310 $130,859 $434,581

2008 $72,545 $117,921 $207,223 $86,452 $484,141

2007 $103,194 $199,032 $173,055 $106,384 $581,665

2006 $19,925 $142,670 $155,785 $148,051 $466,431


The data represented here are sum if functions pulled from another table then used to make a chart.


All I want to do Is copy those formulas 82 rows down over and over again, reference a new piece of information in the sum ifs and have both the data and the chart update. Trouble is that the chart references the data 82 rows up permenately. I dont know if OFFSET can remove the absolute cell referencing but its causing me loss of sleep!


Thanks for all your help.

 

[Luke M]

Is your chart using a OFFSET function? Are you wanting to make a new chart? It's not exactly clear what you are trying to do...

 

[WarpathMechanic]

Apologies.


Sales Office: 0000123456


This data would be used to create a chart.


Q1 Q2 Q3 Q4 Total

2011 $69,307 $104,317 $80,642 $35,109 $289,375

2010 $174,325 $138,097 $281,717 $173,726 $767,865

2009 $114,269 $114,143 $75,310 $130,859 $434,581

2008 $72,545 $117,921 $207,223 $86,452 $484,141

2007 $103,194 $199,032 $173,055 $106,384 $581,665

2006 $19,925 $142,670 $155,785 $148,051 $466,43


What I need to do is change the sales office. The data below would update (using SUMIFS functions). and a new chart is created. This is copy and pasted roughly every 82 rows (there are multiple charts and tables but I need to solve one first.) So the new data would look like...


Sales office: 0000789456213


Q1 Q2 Q3 Q4 Total

2011 $1,970,489 $2,413,009 $2,088,891 $2,453,977 $8,926,366

2010 $3,645,310 $5,329,771 $12,905,898 $4,002,189 $25,883,168

2009 $2,066,737 $2,574,914 $3,039,107 $3,416,344 $11,097,102

2008 $3,207,849 $2,511,133 $3,090,379 $3,176,163 $11,985,524

2007 $3,594,907 $3,173,763 $3,132,105 $3,109,038 $13,009,813

2006 $989,596 $4,095,384 $3,704,033 $3,100,554 $11,889,567


and a chart gets made from this one. The functions work like a charm but the new chart would reference the first data and not follow the new data. I would manually update the chart using "Select Data" from the standard menu however there are about 75-100 charts that need to be made and I was hoping this could be done using formulas or at the very least removing the consistent absolute cell references from the charts.


Currently the charts are NOT using the OFFSET formula and are merely data selections.


I hope this clears it up

 

[NARAYANK991]

Hi ,


Sorry if I am still not clear about your assignment.


1. I understand that you have 75 to 100 sales offices , with similar sets of data for each sales office.


2. The data in these sets gets automatically updated , so the figures are always correct.


3. These sets of data are plotted in a chart ; I am not clear whether you have only one chart , or you have 75 to 100 charts. Can you clarify ?


If I assume you have only one chart , and each time you wish to have a chart for a different sales office , what do you do ?


If I assume you have 75 to 100 charts , and you have already configured the data series for all of them , what is the problem ?


Can you please give more details ?


Narayan

 

[WarpathMechanic]

NARAYANK:


In response to #3, I have 75-100 charts.


Honestly the problem was that even though I had the data configured for all the charts, That would mean that while the data is updated down the sheet, the charts were referencing the first set of data. This would mean rebuilding 72/75 charts (which the number is closer to 300). I can do it manually (select data, make chart, repeat X299) it would just be easier if there was a way that the absolute cell referencing within excel's charting system did not exist and I could have relative referencing so when I copy and pasted the charts it would reference the new cells. I can try and post the sheet here but the trouble is my company has every upload site locked down.

 

[NARAYANK991]

Hi ,


I think that if you designate the data areas as tables , then everything becomes easier ; inserting a chart is just a matter of placing the cursor on any cell within the table , and selecting Insert -> Chart.


I doubt that once you have copied a chart , the data series will reflect anything other than the original data , since what you are doing is making a copy. Unlike a range copy , there is no way that Excel can adjust the data series. I might be wrong.


Narayan

 

[SirJB7]

Hi, WarpathMechanic!

For having one only chart from a selected sales office you can build a drowdown box with each sales office code and the start file (i.e., 00000123456 - 76, 00000234567 - 158, ... asuming that the table is in the last 7 rows of each group of 82, otherewise adjust the first 76), and create the references to the then reference to the 7 by 5 table associated. If you have that structure every each 82 files, you can arrange a formula that contains something like "(ROW()-7)*82+1)". So you may choose the branch and have it updated chart, but I agree that's bases in a one by one selection.

For having multiple ranges (the first with a correct data series chart) and then copied downwards, it seems you've got no choice other than writing a little of VBA code and there update the data series ranges based in analogues formulaes like "(i-7)*82+1" with i as an integer pointing to the first involved row of each group.

Regards!