Re: Timeline chart from text containing cells

From: K. Georgiadis (anonymous_at_discussions.microsoft.com)
Date: 05/26/04 

Date: Wed, 26 May 2004 06:02:42 -0700

Before your response, I was thinking along the lines of:

1) copying the timeline matrix (a work***, not yet
graphed) to a new ***
2) converting the text indicators to "m", "q", and "u",
using nested IF statements
3) formatting the work*** area containing the m, q, u
indicators to "Wingdings" which would make them appear as
square, round and diamond shaped bullets.

Strictly speaking, this would be a work*** that LOOKED
like a chart, not truly a chart.

Your approach is more professional. I'll give it a try
>-----Original Message-----
>So it's like a Gantt chart with products on the vertical
axis, dates on
>the horizontal, and usually with horizontal bars
denoting some time span
>for each product. Only you want some kind of indicator
of RE, EX, FS, ETC.
>
>You need to do a bit of work. I'm assuming it's already
a gantt chart
>with bars. You need to set up a range for each new
series (RE, EX, FS
>each are a separate series) that has a date and a
vertical coordinate
>for each point you need to add. The vertical coordinate
is an integer
>value, 1,2,3, where each number i refers to the ith item
up from the
>bottom in the list of products.
>
>Before proceeding, right click the chart, select Chart
Options, then the
>Axes tab. Under Category Axis, check Category instead of
Automatic.
>
>Copy the range for each new series, select the chart,
and use Paste
>Special from the Edit menu to add the data as a new
series. Right click
>on the new series, choose Chart Type from the pop up
menu, and select
>and XY Scatter type, with markers and no lines.
>
>Excel adds secondary X and Y axes, but often the new Y
axis is hidden
>under the primary X axis. Double click on the secondary
X axis (top of
>chart), and on the scale tab, check the Crosses at
Maximum button. This
>makes the axis visible.
>
>Change the axis scale of this secondary Y axis so the
minimum is 0.5 and
>the maximum is N + 0.5, where N is the number of
categories on the
>primary category axis (left hand vertical axis). On the
patterns tab,
>check None for ticks marks and labels.
>
>Right click the chart, select Chart Options, Axes tab,
and uncheck the
>secondary X axis. Excel will use the gantt chart Y axis
as the X axis
>for the scatter series.
>
>You can either format the new series so each series has
a distinctive
>marker, or you can add the label to the points. Excel XP
and later give
>you a choice of Series Name for the label text; in older
versions you
>have to add value or label, then edit the text. You
don't even need a
>marker, you can format the labels and use the Center
position (Alignment
>tab), and the label serves as the marker.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>Tutorials and Custom Solutions
>http://PeltierTech.com/
>_______
>
>
>K. Georgiadis wrote:
>
>> I have seen great tips here for creating Excel
timeline
>> charts from numerical data. My question may be far
afield
>> but here it is:
>>
>> is there a way to create a timeline chart from a table
>> organized as follows:
>>
>> 1) Years 2004 to 2009 as column headings, columns B-G
>> 2) Product descriptions as row titles in column A
>> 3) keywords (such as "RE", "EX", "FS") in the
>> intersecting cells
>>
>> Can Excel convert these key words to distinctive
symbols
>> (such as a square bullet, round bullet, diamond shaped
>> bullet) and create a timeline chart?
>
>.
>