Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
No Such Luck
 
Posts: n/a
Default 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.
  #2   Report Post  
Earl Kiosterud
 
Posts: n/a
Default

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.



  #3   Report Post  
Earl Kiosterud
 
Posts: n/a
Default

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.





  #4   Report Post  
Jon Peltier
 
Posts: n/a
Default

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.


  #5   Report Post  
No Such Luck
 
Posts: n/a
Default


"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




  #6   Report Post  
No Such Luck
 
Posts: n/a
Default


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







  #7   Report Post  
Jon Peltier
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I graph text information in Excel garrison0013 Charts and Charting in Excel 5 April 3rd 23 03:41 PM
Problem drawing lines on charts Susan Lambert Charts and Charting in Excel 7 December 13th 04 09:29 AM
How can I auto update a graph? Bigxcr Charts and Charting in Excel 2 November 30th 04 08:40 PM
compare unique identifiers in multiple ranges bawilli_91125 Charts and Charting in Excel 1 November 30th 04 07:34 PM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM


All times are GMT +1. The time now is 11:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"