Charting same cell from different sheets

Microsoft Office 2007 home and student
March 24, 2010 at 13:34:32
Specs: Windows Vista
I've searched the net for this answer but I never found any close enough to solve it my self. I have a workbook witch contains several sheets. I the end I have a "summary" sheet to display SUMs of different data. Now I also want a chart that displays X and Y values witch can be found in exact same cell from every sheet. To make an example A1 is X and D2 is Y. X is a week number and Y is "money" ( X = 12 , Y = 1300). Here comes the tricky part. I want this chart to expand by it self as soon as I insert a new sheet witch has values in these cells. The name of the sheets is text, but I can easily change it to weeknumber if it helps a bit e.g. 11. Excel doesnt like when Im using ='sheet1:sheet99'!$E$2. Is there any way to easily create a chart by using 2 cells from each sheet in the workbook and keep the chart dynamicly updated without pull the cell data from all sheets into the sheet containing the chart?

See More: Charting same cell from different sheets

Report •


#1
March 25, 2010 at 12:23:40
Hi again. Just to explain a little bit more. I will always insert a sheet just before the last sheet. I have created two sheets, one in the beggining of the workbook and one in the end, witch I have made hidden afterwards, I thought this might help to selecte the range, but I might be wrong... All new sheets are created from a sheet that I called "ToCopy".
I hope I explained good enough but Ill try once more. In my chart on my summary sheet I want; Each "pillar" is a weeknumber (from a fixed cell) and the value is money (from a fixed cell). So if I would have 23 sheets my chart should show me 23 "pillars" (bars, plot?(Im from Sweden =D )).

So I kneel before you excel masters, please help me before Ill go crasy.


Report •

#2
March 26, 2010 at 08:12:54
Hi,

I have tried various ways to create a chart that references data on multiple worksheets and updates when a new worksheet is added.

In the end the only way that I could make this work was to use a macro.
At this stage the macro is run from a button after a new worksheet has been added. If this works, it may be possible to make the process automatic by triggering the macro by the New Sheet Event.

This macro has the advantage that it does not matter in what order or where a new worksheet is added. It sorts the data according to the values in cell A1 on each worksheet.

You do not need any hidden worksheets, and data on the Summary and Copy worksheets is ignored.

The Summary worksheet has a Chart called Chart1 - To find out the system name for the chart, click any cell on the Summary worksheet. Now hold down the Ctrl key and click the chart. The Chart name will appear in the box that shows cell addresses above the first column and row labels.

If this is the first chart on this worksheet it will be "Chart 1".
If it is different, then change this line in the macro:

'adjust the series references in the chart
' Change the name if not "Chart1"
With Worksheets("Summary").ChartObjects("Chart 1").Chart

Note that the chart has only one data series. This macro updates series 1 only.
Make your chart series 1 point to Cells A1 to A2 for the week number and Cells B1 to B2 for the data, both on the Summary worksheet.

Add a button to your "Summary" worksheet as follows:
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit

Private Sub Button1_Click()
Dim wsEach As Worksheet
Dim rngDest As Range
Dim rngWeekData As Range
Dim rngMoneyData As Range
Dim intRow As Integer
Dim strSeries As String
Dim strSeriesLeft As String
Dim strSeriesRight As String

On Error GoTo ErrHnd

'turn off screen updating to stop flicker
Application.ScreenUpdating = False

'set the start of the destination data
Set rngDest = Worksheets("Summary").Range("A1")

'set row offset counter to zero
intRow = 0

'loop through all worksheets except 'Summary' and 'Copy'
For Each wsEach In ActiveWorkbook.Worksheets()
    'exclude Summary and Copy
    If wsEach.Name <> "Summary" And wsEach.Name <> "Copy" Then
        'get the week number and copy it to the Summary worksheet - column A
        wsEach.Range("A1").Copy Destination:=rngDest.Offset(intRow, 0)
        'get the money data and copy it to the Summary worksheet - column B
        wsEach.Range("D2").Copy Destination:=rngDest.Offset(intRow, 1)
        'increment the column number
        intRow = intRow + 1
    End If
Next wsEach

'create ranges for the data
'week numbers
Set rngWeekData = rngDest.Resize(intRow, 1)
'money
Set rngMoneyData = rngDest.Offset(0, 1).Resize(intRow, 1)

'now sort the data into week number order
Union(rngWeekData, rngMoneyData).Sort Key1:=rngWeekData, Order1:=xlAscending

'adjust the series references in the chart
' Change the name if not "Chart1"
With Worksheets("Summary").ChartObjects("Chart 1").Chart
    
    strSeries = .SeriesCollection(1).Formula
    strSeriesLeft = Left(strSeries, InStr(1, strSeries, ","))
    'arbitrary way to get text after last ","
'assume last comma is in the last 5 characters
    strSeriesRight = Right(strSeries, Len(strSeries) - (InStr(Len(strSeries) - 5, _
            strSeries, ",")) + 1)
    'recreate the series 'formula' with new range information
    .SeriesCollection(1).Formula = _
            strSeriesLeft & _
            "Summary!" & rngWeekData.Address & ",Summary!" & rngMoneyData.Address & _
            strSeriesRight
End With

'turn screen updating on again
Application.ScreenUpdating = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
'turn screen updating on again
Application.ScreenUpdating = True
End Sub

Note that Sub Button1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful

As Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

Remove all hidden and unused worksheets.

After selecting any cell, the new command button should now respond to a click and run the macro.

If this works, add another sheet with a new week number in A1 and new data in D2.
Run the macro again.

Regards


Report •

#3
March 26, 2010 at 13:03:50
Humar, you are awsome!!! It works exactly as I want it. Thank you so much.

Report •

Related Solutions

#4
March 27, 2010 at 04:24:33
Hi,

You're welcome. Glad it works.

I have a replacement for part of the code that you can try if it is of use to you.

The original code copies and pastes the data from each worksheet. This replacement creates links to the data on each worksheet.

This means that any changes made to the data on the individual worksheets after they were added will show up in the Summary worksheet and the chart will automatically update.

'loop through all worksheets except 'Summary' and 'Copy'
For Each wsEach In ActiveWorkbook.Worksheets()
    'exclude Summary and Copy
    If wsEach.Name <> "Summary" And wsEach.Name <> "Copy" Then
        'get the week number and Link Summary worksheet to it in column A
        wsEach.Range("A1").Copy
        rngDest.Offset(intRow, 0).Select
        ActiveSheet.Paste Link:=True
        'get the money data and Link Summary worksheet to it in column B
        wsEach.Range("D2").Copy
        rngDest.Offset(intRow, 1).Select
        ActiveSheet.Paste Link:=True
        'increment the column number
        intRow = intRow + 1
    End If
Next wsEach

Regards

Humar


Report •

Ask Question