Download data to have the graph generated automatically

[Tamrak]

Good morning,

I would like to add a “nice to have” feature into my bubbles chart.

I download text data into Datasheet1.xls. It contains four columns: Project_Name (A), Cost (B), Score (C), Labor_Hours (D). This file will be overridden each time the download occurs. So, the number of rows will be different. However, the number of columns will remain the same.

I created another worksheet, called MyChart.xls. This file will contain the graphic information. The source data comes from the four columns in Datasheet1.xls.

When I plotted the graph series (in MyChart.xls), the X values and the Y values are fixed (static). I have to continue manually changing the number based on the number of rows given.

Sometimes, I have more rows than previous download. Sometimes, I have fewer rows.

What I would like to accomplish is to have the macros or VBA codes embedded into MyChart.xls by recognizing the last row of the data. (The row that is blank should be the end of series and it should not be plotted.) In this case, the X and Y values will be done automatically.

For example -: in the current

X Values ='Datasheet1'!$B$5:$B$20
Y Values ='Datasheet1'!$C$5:$C$20

Next download, I might have five rows less. It should automatically be changing to:

X Values ='Datasheet1'!$B$5:$B$15
Y Values ='Datasheet1'!$C$5:$C$15

The same thing happens; I might have 10 rows more on the next one. It should be automatically changing to:

X Values ='Datasheet1'!$B$5:$B$25
Y Values ='Datasheet1'!$C$5:$C$25

I do appreciate your assistant regarding modifications of this feature. Thank you.

 

[SkipVought]

Hi,

Check out How can I rename a table as it changes size faq68-1331.

Use the Named Ranges in your Chart - Source Data references. For instance, if Sheet1!A2:A99 is one seriec referencem then use the named range in the reference like this
[tt]
=Sheet1!YourRangeName
[/tt]


Skip,

_[red][/red]