Previous Topic Next Topic

Relative worksheet reference in formula for chart data ranges?

Closed
Collin10592 Posts 4 Registration date Wednesday July 23, 2014 Status Member Last seen July 29, 2014 - Jul 23, 2014 at 11:22 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 - Jul 31, 2014 at 11:13 AM
Hello,

I am working on a project and was hoping for some help any and all excel wizzes if you have the time. Does anyone know how or if one can reference the current sheet in a formula for a chart data range?

I am making a large number of graphs; each one references data on the same sheet, but there are 12 different sheets of data and graphs. They are all formatted the same, so I am wondering if there is a way to use a formula in the chart data range that references specific cells (A27:AI37, for example) on whatever sheet I paste the graphs to; that way I will only have to make them once. In other words, I want to make the graphs on the first sheet and then paste them to the other 11 and not have to re-select the data range for each of them.

Any advice would be greatly appreciated. Thanks a lot!

-Collin
Related:

5 responses

Answer 1 / 5
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
Jul 24, 2014 at 10:16 AM
Hi Collin,

The following code will change the data range: 
Sub AdjustGraphRange()
ActiveChart.SetSourceData Source:=Range("A27:AI37")
End Sub


So after pasting your graph, run the code.

It's also possible to select a shortcut for the code in the macro select window (Alt+F8).

Best regards,
Trowa
Answer 2 / 5
Collin10592 Posts 4 Registration date Wednesday July 23, 2014 Status Member Last seen July 29, 2014
Jul 29, 2014 at 11:29 AM
Trowa,

thanks a lot for your help! So this code will need to be run for each graph. correct? Is there a way to paste all 60 graphs and have the code change all of their respective data ranges to the same cells on the new sheet?

thanks,
Collin
Answer 3 / 5
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
Jul 29, 2014 at 11:44 AM
Hi Collin,

For that I need a little bit more info.
Did you already create the sheets? If not how do you want to name them.

What I have in mind is that you manually copy the graph, then run the code.
The code will loop through all the sheets, pasting and adjusting the graph.
Are there any sheet names which need the excluded from the loop (i.e. don't need a graph pasted in it)? The sheet which has the original graph would be one of them.

Best regards,
Trowa
Answer 4 / 5
Collin10592 Posts 4 Registration date Wednesday July 23, 2014 Status Member Last seen July 29, 2014
Jul 29, 2014 at 11:50 AM
Trowa,

the first two sheets, "Documentation" and "ABC SCM Top 10-linked" do not need graphs. the third sheet, "Top10 Comparison-Total Rev" already has all 60 graphs that I would like to copy to the next 8 sheets: "Top10 Comparison-Total Expense", "Top10 Comparison-per student"... and so on.

thanks,
Collin
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
Jul 29, 2014 at 12:05 PM
Wait what... you have 60 graphs on a single sheet all with the same range!

So you want to copy 60 graphs from one sheet to the next 8 adjusting the range along the way.

Or am I misinterpreting you.

Never done that before, will get back to you if I can make that work.
Answer 5 / 5
Collin10592 Posts 4 Registration date Wednesday July 23, 2014 Status Member Last seen July 29, 2014
Jul 29, 2014 at 02:09 PM
Trowa,

Here is a picture. Each sheet has 60 graphs, each with its own range from the data above. Each sheet will have the same cell ranges for the graphs but the data is different on each sheet. Let me know if this helps.

Thanks a lot,
Collin



TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
Jul 31, 2014 at 11:13 AM
Hi Collin,

I just thought of a simple solution.

Copy your original Graph sheet 8 times and then copy the data ranges to their correct spot.

This will keep all 60 graph at the same location and also their data ranges. All that is left, is to change the data in the data ranges.

PS. next time posting a picture, find "325px" at the end of the pictures code line and change it to "500px" to increase its size.

Best regards,
Trowa

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses