Timeline chart from text containing cells

  • Thread starter Thread starter K. Georgiadis
  • Start date Start date
K

K. Georgiadis

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?
 
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/
_______
 
Before your response, I was thinking along the lines of:

1) copying the timeline matrix (a worksheet, not yet
graphed) to a new sheet
2) converting the text indicators to "m", "q", and "u",
using nested IF statements
3) formatting the worksheet 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 worksheet that LOOKED
like a chart, not truly a chart.

Your approach is more professional. I'll give it a try
 
That works too. I have a big project right now, a highly customized
gantt chart, which is too complex for a chart, because of an excessive
number of labels. The effect is based on coloring of worksheet cells. It
needs VBA because there are too many conditions for conditional
formatting. But the result is awesome.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Coloring the cells with different colors was my initial
thought but, unfortunately, my VBA skills are too
rudimentary to figure out the procedures.
 
As I stated in my previous post, my VBA skills are
rudimentary but I ordered J. Walkenbach's "VBA for Excel"
and I am hoping to learn a little more over the next few
weeks.
Meanwhile, can anyone point me in the right direction
with the VBA code needed to color cells a certain color,
depending on the corresponding cell content of another
worksheet:

Namely;
1) Worksheet A has a matrix with years running across the
top and product names running down column A.
2) Intersecting cells contain four (4) key words: EX, FS,
GE, or RE
3) the same matrix is copied over to a worksheet B but,
instead of keywords, I now simply want to shade the cells
different colors, depending on the keywords contained in
the parent sheet: maroon if cell content is "EX," red if
cell content is "GE," blue if cell content is "RE" and
green if cell content is "FS."

Thanks in advance.
 
Email me offline (remove caps from the email address) and I'll send a
sample. You can use conditional formatting for up to three colors
(besides the default), and VBA will get you a total of 56.

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