Problem with graph ranges
 

I actually have two problems related to graph ranges:

1) I have a 6-column text file, each column separated by tabs (\t) and
each row separated by newlines (\n). However, each column does not
have the same number of entries. When a column has less entries than
the longest column of the 6, no data is present, just the "\t" to move
on to the next column. I then select this entire 6-column text file
and paste it into Excel. The cells line up perfectly, and the data
looks correct. The trouble I am having is when I select an entire
column by clicking the top of it to create an area graph, the range of
the graph is always from 1 to the number of entries in the longest
column. It appears as though the "\t" with no data before it is being
read (incorrectly) as a 0. I need the graph to range from 1 to the
number of entries in the column selected. Is there a way to fix this?

2) I will eventually have many of these 6-column text files. Not only
are the columns of differing lengths within each text-file, but the
columns will have differing lengths across different text files (i.e.,
just assume that any column from any text file is a random length). I
created a workbook that had 6 area charts based on the columns 1-6 of
one of these text files. I was hoping that I could just replace the
data with data from another text file, and the 6 area charts would
automatically update. Well, they half-way do. The data in the charts
update, but the ranges do not. The ranges continue to be the ranges
that were from the first text file used to initially create the
charts. Is there a way to fix this? I really don't want to (or think I
should have to) create 6 new charts for every text file.

Thanks for any help you can provide.

This is also posted in excel.charting

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"No Such Luck" wrote in message
om...
I actually have two problems related to graph ranges:

1) I have a 6-column text file, each column separated by tabs (\t) and
each row separated by newlines (\n). However, each column does not
have the same number of entries. When a column has less entries than
the longest column of the 6, no data is present, just the "\t" to move
on to the next column. I then select this entire 6-column text file
and paste it into Excel. The cells line up perfectly, and the data
looks correct. The trouble I am having is when I select an entire
column by clicking the top of it to create an area graph, the range of
the graph is always from 1 to the number of entries in the longest
column. It appears as though the "\t" with no data before it is being
read (incorrectly) as a 0. I need the graph to range from 1 to the
number of entries in the column selected. Is there a way to fix this?

2) I will eventually have many of these 6-column text files. Not only
are the columns of differing lengths within each text-file, but the
columns will have differing lengths across different text files (i.e.,
just assume that any column from any text file is a random length). I
created a workbook that had 6 area charts based on the columns 1-6 of
one of these text files. I was hoping that I could just replace the
data with data from another text file, and the 6 area charts would
automatically update. Well, they half-way do. The data in the charts
update, but the ranges do not. The ranges continue to be the ranges
that were from the first text file used to initially create the
charts. Is there a way to fix this? I really don't want to (or think I
should have to) create 6 new charts for every text file.

Thanks for any help you can provide.

Oops. I meant to post about the multipost in .excel.misc.

I don't understand "graph range." Describe the resulting graph. What kind
of chart are you selecting. Tell us in Source Data whether the series are
specified in columns or rows.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Earl Kiosterud" wrote in message
...
This is also posted in excel.charting

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"No Such Luck" wrote in message
om...
I actually have two problems related to graph ranges:

1) I have a 6-column text file, each column separated by tabs (\t) and
each row separated by newlines (\n). However, each column does not
have the same number of entries. When a column has less entries than
the longest column of the 6, no data is present, just the "\t" to move
on to the next column. I then select this entire 6-column text file
and paste it into Excel. The cells line up perfectly, and the data
looks correct. The trouble I am having is when I select an entire
column by clicking the top of it to create an area graph, the range of
the graph is always from 1 to the number of entries in the longest
column. It appears as though the "\t" with no data before it is being
read (incorrectly) as a 0. I need the graph to range from 1 to the
number of entries in the column selected. Is there a way to fix this?

2) I will eventually have many of these 6-column text files. Not only
are the columns of differing lengths within each text-file, but the
columns will have differing lengths across different text files (i.e.,
just assume that any column from any text file is a random length). I
created a workbook that had 6 area charts based on the columns 1-6 of
one of these text files. I was hoping that I could just replace the
data with data from another text file, and the 6 area charts would
automatically update. Well, they half-way do. The data in the charts
update, but the ranges do not. The ranges continue to be the ranges
that were from the first text file used to initially create the
charts. Is there a way to fix this? I really don't want to (or think I
should have to) create 6 new charts for every text file.

Thanks for any help you can provide.

You could look into dynamic named ranges for your source data ranges. Here are a few
examples and a lot of links:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

No Such Luck wrote:

I actually have two problems related to graph ranges:

1) I have a 6-column text file, each column separated by tabs (\t) and
each row separated by newlines (\n). However, each column does not
have the same number of entries. When a column has less entries than
the longest column of the 6, no data is present, just the "\t" to move
on to the next column. I then select this entire 6-column text file
and paste it into Excel. The cells line up perfectly, and the data
looks correct. The trouble I am having is when I select an entire
column by clicking the top of it to create an area graph, the range of
the graph is always from 1 to the number of entries in the longest
column. It appears as though the "\t" with no data before it is being
read (incorrectly) as a 0. I need the graph to range from 1 to the
number of entries in the column selected. Is there a way to fix this?

2) I will eventually have many of these 6-column text files. Not only
are the columns of differing lengths within each text-file, but the
columns will have differing lengths across different text files (i.e.,
just assume that any column from any text file is a random length). I
created a workbook that had 6 area charts based on the columns 1-6 of
one of these text files. I was hoping that I could just replace the
data with data from another text file, and the 6 area charts would
automatically update. Well, they half-way do. The data in the charts
update, but the ranges do not. The ranges continue to be the ranges
that were from the first text file used to initially create the
charts. Is there a way to fix this? I really don't want to (or think I
should have to) create 6 new charts for every text file.

Thanks for any help you can provide.

"Jon Peltier" wrote in message
...
You could look into dynamic named ranges for your source data ranges. Here
are a few examples and a lot of links:

http://peltiertech.com/Excel/Charts/Dynamics.html


Thanks, Jon.

I've actually tried several of the dynamic range tutorials without luck.
They all seemed to be geared toward a graph with data values and labels, but
I haven't been able to taylor them to work with my scenario. Could you,
perhaps, provide a very, very simple dynamic range solution for just a
column of data that will change in length (i.e., no labels, no column
headers (and thus, offsets), etc.) Just a simple column like:

4
3
8
1
6
1
4

whose area graph would automatically expand if you added two elements:

4
3
8
1
6
1
4
8
1

"Earl Kiosterud" wrote in message
...
Oops. I meant to post about the multipost in .excel.misc.

I don't understand "graph range." Describe the resulting graph. What
kind of chart are you selecting. Tell us in Source Data whether the series
are specified in columns or rows.

I would like each of the 6 columns to be graphed as a a separate area chart
(the x axis is the row#, the y axis is the data value). The "X" axis range
of each of the 6 area charts, as it stands now, is from "1" to the number of
the elements in the longest column. This is wrong. I need the range of each
of the 6 area charts to be from "1" to the number of elements in the column
that was used to create the area chart.

Small Example of the problem:

Col A has 250 elements
Col B has 500 elements

If I select column A by clicking the "A" at the top of the table, and create
an area graph, the "X" range for the area graph is from "1" to "500", not
"1" to "250". I need it to be from 1 to 250, as it should be. What
instruction will tell excel to only graph "non-blank" data in a column? This
seems like a trivial request, and I'm not sure why Excel is not doing it by
default.


--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Earl Kiosterud" wrote in message
...
This is also posted in excel.charting

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"No Such Luck" wrote in message
om...
I actually have two problems related to graph ranges:

1) I have a 6-column text file, each column separated by tabs (\t) and
each row separated by newlines (\n). However, each column does not
have the same number of entries. When a column has less entries than
the longest column of the 6, no data is present, just the "\t" to move
on to the next column. I then select this entire 6-column text file
and paste it into Excel. The cells line up perfectly, and the data
looks correct. The trouble I am having is when I select an entire
column by clicking the top of it to create an area graph, the range of
the graph is always from 1 to the number of entries in the longest
column. It appears as though the "\t" with no data before it is being
read (incorrectly) as a 0. I need the graph to range from 1 to the
number of entries in the column selected. Is there a way to fix this?

2) I will eventually have many of these 6-column text files. Not only
are the columns of differing lengths within each text-file, but the
columns will have differing lengths across different text files (i.e.,
just assume that any column from any text file is a random length). I
created a workbook that had 6 area charts based on the columns 1-6 of
one of these text files. I was hoping that I could just replace the
data with data from another text file, and the 6 area charts would
automatically update. Well, they half-way do. The data in the charts
update, but the ranges do not. The ranges continue to be the ranges
that were from the first text file used to initially create the
charts. Is there a way to fix this? I really don't want to (or think I
should have to) create 6 new charts for every text file.

Thanks for any help you can provide.

A simplistic approach would be something like this. In the Define Names dialog
(Insert - Names - Define, or CTRL+F3), define a name such as this:

Name: ColumnA
Refers To:
=OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A),1)

Then in step 2 of the chart wizard, on the Series tab, for Values enter the
sheet-qualified range name:

=Sheet1!ColumnA

This could be readily automated using VBA:

Sub ChartDynamicColumns()
Dim rCol As Range
Dim rColName As Range
Dim sCol As String

Application.ScreenUpdating = False

For Each rCol In ActiveSheet.Columns
sCol = rCol.Address(ColumnAbsolute:=False)
sCol = Left(sCol, (Len(sCol) - 1) / 2)

ActiveWorkbook.Names.Add _
Name:="'" & ActiveSheet.Name & "'!Column" & sCol, _
RefersTo:="=OFFSET('" & ActiveSheet.Name & "'!$" & sCol & _
"$1,0,0,MAX(1,COUNT('" & ActiveSheet.Name & "'!$" & _
sCol & ":$" & sCol & ")),1)"

Set rColName = ActiveSheet.Range("Column" & sCol)
If WorksheetFunction.Count(rColName) 0 Then
With ActiveSheet.ChartObjects.Add(rColName.Left, 50, 200, 150).Chart
If .SeriesCollection.Count = 0 Then
.SeriesCollection.NewSeries
Else
Do While .SeriesCollection.Count 1
.SeriesCollection(1).Delete
Loop
End If
With .SeriesCollection(1)
.Values = "='" & ActiveSheet.Name & "'!Column" & sCol
End With
.ChartType = xlArea
End With
End If
Next

Application.ScreenUpdating = True
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

No Such Luck wrote:
"Jon Peltier" wrote in message
...

You could look into dynamic named ranges for your source data ranges. Here
are a few examples and a lot of links:

http://peltiertech.com/Excel/Charts/Dynamics.html



Thanks, Jon.

I've actually tried several of the dynamic range tutorials without luck.
They all seemed to be geared toward a graph with data values and labels, but
I haven't been able to taylor them to work with my scenario. Could you,
perhaps, provide a very, very simple dynamic range solution for just a
column of data that will change in length (i.e., no labels, no column
headers (and thus, offsets), etc.) Just a simple column like:

4
3
8
1
6
1
4

whose area graph would automatically expand if you added two elements:

4
3
8
1
6
1
4
8
1