excel chart - absolute referencing????!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form I created in VB and it facilitates data entry into a
spreadsheet. Once I fill out the textboxes and click ok, it inserts a new row
into the spreadhseet with the new info below the second row. So all the other
rows containing previously entered info move down. I need to make a chart
that graphs ONLY the most recently inserted rows content. I tried to turn
off absolute referencing in the Data series tab, but every time a new row is
inserted the graph is not redrawn , because it keeps the formerly entered
info from the cells that were in the first row, but are now in the second row
....

I hope I was clear enough...

Anyone knows how to go about fixing this problem?!
 
Modifying your chart to use dynamic named ranges should solve the problem.
For example, assume your X-Axis lables are in the range A1:C1. Your data is
in the range A2:C2. Create a simple bar chart based on the range A1:C2.

In Excel 2003, assume your workbook is named "Test.xls" and you data is on
"Sheet1". Go to Insert -> Name - Define.

First, create a named range called "Titles" on the range A1:C1. The formula
for that named range should look like this:

=Sheet1!$A$1:$C$1

Second, create another named range called "Data". The formula for that
named range should look like this:

=OFFSET(Titles,1,0,1,3)

Now go to your chart. In the Source Data dialog box, enter the following as
the source for the data series values:

=Test.xls!Data

For more information, Jon Peltier provides a series of resources on dynamic
charting via this link below:

http://peltiertech.com/Excel/Charts/DynamicChartLinks.html
 
Back
Top