# Trouble making chart dynamic

#### riedyp

##### Board Regular
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
88.3 KB · Views: 6

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### Jon Peltier

##### MrExcel MVP
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
Hello,

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

#### Attachments

• mrexcel.PNG
107.2 KB · Views: 4

#### Jon Peltier

##### MrExcel MVP
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

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
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

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
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
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
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.

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

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)

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.

Replies
0
Views
120
Replies
2
Views
284
Replies
5
Views
216
Replies
13
Views
220
Replies
1
Views
342

1,127,457
Messages
5,624,855
Members
416,063
Latest member
chaulon199

### 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.

### Which adblocker are you using?

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

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