Trouble making chart dynamic

riedyp

Board Regular
Joined
Feb 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Trying to make my "time spent versus time allocated chart" update when new values are inputted in the chart below it.
Cells K35:N60 are populated with formulas that will only show a value if they are inputted on another sheet.

K35: =IF(ROW()-ROW(K$34)<=SUM(IFERROR(--(('Project - Gantt Chart'!$B$9:$B$45/INT('Project - Gantt Chart'!$B$9:$B$45))=1),"")),ROW()-ROW(K$34),"")
L35: =IFERROR(INDEX('Project - Gantt Chart'!$B$9:$I$45,MATCH('Executive Summary'!$K35,'Project - Gantt Chart'!$B$9:$B$45,0),4),"")
M35: =IFERROR(INDEX('Project - Gantt Chart'!$B$9:$I$45,MATCH('Executive Summary'!$K35,'Project - Gantt Chart'!$B$9:$B$45,0),5),"")
N35: =IFERROR(INDEX('Project - Gantt Chart'!$B$9:$I$45,MATCH('Executive Summary'!$K35,'Project - Gantt Chart'!$B$9:$B$45,0),8),"")

Below is the code I have been trying to use. I don't really have any other insights on how to go about this so any suggestion would be more than welcomed! Thank you.

VBA Code:
Private Sub TimeSpent()
Dim ch As ChartObject
Set ch = Worksheets("Executive Summary").ChartObjects("Chart 8")
LastRow = Worksheets("Executive Summary").Columns("J").Find(1, SearchDirection:=xlPrevious, LookIn:=xlValues, LookAt:=xlWhole).Row

Worksheets("Executive Summary").ChartObjects("Chart 8").Activate

    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Name = Worksheets("Executive Summary").Range("L34")
    ActiveChart.FullSeriesCollection(1).Values = Range(Cells(35, 12), Cells(LastRow, 12))
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = Worksheets("Executive Summary").Range("M34")
    ActiveChart.FullSeriesCollection(2).Values = Range(Cells(35, 13), Cells(LastRow, 13))
    ActiveChart.HasLegend = True
End Sub
 

Attachments

  • time2.PNG
    time2.PNG
    88.3 KB · Views: 6

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
What are the series in the chart?
Do the series names change, or just the number of points in each series?

It looks like you want two series:
=SERIES('Executive Summary'!$L$34,'Executive Summary'!$K$35:$K$40,'Executive Summary'!$L$35:$L$40,1)
=SERIES('Executive Summary'!$M$34,'Executive Summary'!$K$35:$K$40,'Executive Summary'!$M$35:$M$40,2)

When phase 7 is added, the last row will become 41. Assuming your code discovers it correctly (if column J includes formulas that look like "" when there's nothing in the other worksheet, it might not), then your code could be as simple as this:

VBA Code:
Private Sub TimeSpent()
    Dim ws As Worksheet
    Set ws = Worksheets("Executive Summary")
    Dim ch As Chart
    Set ch = ws.ChartObjects("Chart 8").Chart
    Dim LastRow As Long
    LastRow = ws.Columns("J").Find("*", SearchDirection:=xlPrevious, LookIn:=xlValues, LookAt:=xlWhole).Row

    ch.SeriesCollection(1).XValues = ws.Range(ws.Cells(35, 11), ws.Cells(LastRow, 11))
    ch.SeriesCollection(1).Values = ws.Range(ws.Cells(35, 12), ws.Cells(LastRow, 12))

    ' don't need to repeat XValues, which are shared by all series
    ch.SeriesCollection(2).Values = ws.Range(ws.Cells(35, 13), ws.Cells(LastRow, 13))
End Sub
 
Last edited:

riedyp

Board Regular
Joined
Feb 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Hello,

any idea why i keep getting this error? Your code looks like it is exactly what i want to accomplish.
 

Attachments

  • mrexcel.PNG
    mrexcel.PNG
    107.2 KB · Views: 4

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Oops, didn't test it, just copied what you started with.

I changed

VBA Code:
.Find(1,

to

VBA Code:
.Find("*",

and the error went away.
 

riedyp

Board Regular
Joined
Feb 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hm, I still encounter the same error message for some reason, any idea why this may be occuring?

This is the what the formula is in cells in column J. So J35=IF(K35<>"",1,"")
 
Last edited:

riedyp

Board Regular
Joined
Feb 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Would it be because I did not "Set" "LastRow" ? Because when i do "Set" it i get the error "Object required".
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

No, it's not "Set".

These two lines work fine for me, with the same kind of formula in column J:

VBA Code:
    Dim LastRow As Long
    LastRow = ws.Columns("J").Find("*", SearchDirection:=xlPrevious, LookIn:=xlValues, LookAt:=xlWhole).Row
 

riedyp

Board Regular
Joined
Feb 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Thank you, it worked. The reason I was having issues is because I changed the formatting of the all of the "1"s in column J to be hidden with ";;;" but that caused the error. So I just switched their text color to white and it worked fine. Is there a way for me to make this code be autoupdated when any changes are made?
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Right click on this worksheet's tab, and select View Code. When the code module for the worksheet appears, select Worksheet from the left-hand dropdown at the top of the window, then select Calculate from the right-hand dropdown. In the Worksheet_Calculate stub that is generated, enter the name of this procedure. Whenever a value on this worksheet changes because of a calculation, the Worksheet_Calculate sub will run, and this will run your other code.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Here's another approach that doesn't require VBA, and it updates automatically when the data updates. It uses Names (a/k/a "named ranges") that grow with the data.

I've added a formula in cell J34:
=COUNT(J35:J65)
which counts the number of cells in column J next to the data that contain a numeric value. This formula doesn't care if you use a number format of ;;; to prevent the numbers from displaying, it counts the underlying values. This cell tells me how many rows are in the Names.

Go to the Formulas tab, click Define Name. In the dialog enter these:

Name: Phase
Refers To:
=OFFSET($K$34,1,0,$J$34,1)

This means Phase is the name of the range that, with respect to $K$34 starts one row down and zero columns right, and it is $J$34 rows high and 1 column wide. So in your screenshot, it is range K35:K40.

Add these two Names:

Name: HoursAllocated
Refers To:
=OFFSET(Phase,0,1)

Name: HoursSpent
Refers To:
=OFFSET(Phase,0,2)

So HoursAllocated is the range zero rows down and one column to the right of Phase (if you don't specify height and width, it uses the size of the reference range Phase), and HoursSpent is the range zero rows down and two columns to the right.

Make your chart. It should have two series with these series formulas:
=SERIES('Executive Summary'!$L$34,'Executive Summary'!$K$35:$K$40,'Executive Summary'!$L$35:$L$40,1)
=SERIES('Executive Summary'!$M$34,'Executive Summary'!$K$35:$K$40,'Executive Summary'!$M$35:$M$40,2)

Edit these right in the formula bar so you have:
=SERIES('Executive Summary'!$L$34,'Executive Summary'!Phase,'Executive Summary'!HoursAllocated,1)
=SERIES('Executive Summary'!$M$34,'Executive Summary'!Phase,'Executive Summary'!HoursSpent,2)

After you hit Enter for each formula, Excel changes 'Executive Summary' in front of each Name to the workbook name, but that's not an issue. Also, you don't need to change K35:K40 to Phase in the second formula, since Excel uses the same X values for both series in a column chart.

When the data updates and another 1 is added to column J, cell J34 increases by one, the Names extend one row lower, and the chart updates accordingly.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,695
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top