Discussion on:

Two ways to build dynamic charts in Excel

I have a simple main form which derives the value from a another form's combo box. The main form has a subform . I have a conditional macro on the subform "On Open" event which works when I open the subform separately but when the Main form is opened through the combo form I get an error "that the control name could not be found/ it is mis spelt .... " Why does the Main form not read the sub form control name or the macro of the sub form?

The accompanying Excel spreadsheets are wrong. The series for Michaels and the series for Hancock are identical. Hancock should be the series adjacent to Michaels. Other than that one error, this is a very useful article. Thank You.

I just use a mixed (absolute and relative) range for the series. For example, the first series when you have 3 rows of data could be DynamicChart1!$A$2:$A5 (no dollar sign for the 5). I start out with one more row in the range than I actually have of data. I leave the last row blank or indicate that it is a place holder. To add more data I select the place holder row and insert a new one above it. to eliminate any ill effect from having the last (non-data) row included in the chart you can use zero or functions like MAX($A$2:$A4), MIN($A$2:$A4), or AVERAGE($A$2:$A4), depending on the chart type and how it is used.

As a hardware engineer, I guess I must be daft. I tried using the table method several times and followed the instructions to the letter, but when I added the March row and data, the chart did not dynamically update. I looked for a setting in Office/Excel 2010 options, but could find nothing. Is it a setting?

Excel > Options > Proofing
Click the "AutoCorrect Options" button
In the dialog box that pops up, select the second tab, "AutoFormat As You Type"
Make sure the box for "Include New Rows and Columns in Table" is checked

Susan's table method worked fine for me (XP/Excel 2010)

I discovered that the table was not created correctly, so I guess I didn't follow Susan's instructions to the letter after all.

Thanks for the help!

I can see applications for this already. I have a current project kicking off that I could use this for until I get the web application built for it. Thanks Susan!

Dynamic charting has tremendous potential for creating user-friendly apps that return good visual information for little effort.

The dynamic formula method is great. I've been playing with this and trying to figure it out on a banking spreadsheet I maintain. The problem is, I've been maintaining the sheet for over 2 years now, so I have a lot of data in it. I don't want to lose the old data, but I don't always want to see the old data in my charts. I was able to create the lists using the offset, but I could never use them in the tables. It turns out the only thing I was doing wrong was entering the names without a sheet reference.

By the way, here are the formulas I used to select the data for the charts:

DateSeries: OFFSET(Sheet1!$A$1,StartDateRowNum-1,0,EndDateRowNum-StartDateRowNum+1)
PaymentSeries: OFFSET(Sheet1!$C$1,StartDateRowNum-1,0,EndDateRowNum-StartDateRowNum+1)
PostedSeries: OFFSET(Sheet1!$D$1,StartDateRowNum-1,0,EndDateRowNum-StartDateRowNum+1)
StartDateRowNum: MATCH(StartDate,OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A)),0)
EndDateRowNum: MATCH(EndDate,OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A)),0)

The StartDate and EndDate are named cells that contain the statement dates for the range.

I always started my offset at the first cell in the row. Then I would define a row number after that to start the data. This proved easier than trying to do an "indirect" and "address" function to try to set the starting point.