This actually is a step towards my own best practices. I have multiple data
tables, all linked to the original or raw data. The others will be optimized
for charting, for on-screen display, for printed output, for exporting to
Word, and for whatever other uses I may have. Worksheets are cheap, and the
time spent setting up a linked and formatted table is much much less than
the time spent trying to make one table serve multiple purposes. I repeat my
first line:
If you spend five minutes fixing up your data, you will save five hours of
frustration trying to make the unfixed data work.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"Don F." wrote in message
...
Thanks for the quick reply. Not what I had hoped to hear but confirms
what I
found. It can be done but time consuming and cumbersome. So I will have
two
tables - one to present / maintain data in table format and another to
chart.
Thanks again.
"Jon Peltier" wrote:
Rule of thumb:
If you spend five minutes fixing up your data, you will save five hours
of
frustration trying to make the unfixed data work. I think in the long run
it
will be easier if your data is converted to two columns, one with date,
the
other with values.
Even to do this without rearranging the main block of data requires
adding
dummy rows to get the data to flow from one year to the next. For
example, I
inserted three rows, and in cell B2 (assuming it all starts in B1) I
entered
this formula:
=DATE($A5,MATCH(B$1,$B$1:$M$1,0),1)
and I filled this right and down to fill in all the dates.
YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
1/1/1992 2/1/1992 3/1/1992 4/1/1992 5/1/1992 6/1/1992 7/1/1992
8/1/1992
9/1/1992 10/1/1992 11/1/1992 12/1/1992
1/1/1993 2/1/1993 3/1/1993 4/1/1993 5/1/1993 6/1/1993 7/1/1993
8/1/1993
9/1/1993 10/1/1993 11/1/1993 12/1/1993
1/1/1994 2/1/1994 3/1/1994 4/1/1994 5/1/1994 6/1/1994 7/1/1994
8/1/1994
9/1/1994 10/1/1994 11/1/1994 12/1/1994
1992 164278 164864 164221 165084 165920 166544 167284 168184 170158
170859 171531 173598
1993 175683 174121 172829 176710 178999 178772 180987 180996 182227
183233 185266 186538
1994 185948 188562 191756 192275 191300 193606 194229 196568 197876
200281 200301 201139
Then I selected row 2, and held CTRL while selecting row 5, and I made an
XY
chart. Then I selected row 3 and CTRL selected row 6, copied this,
selected
the chart, and used Edit menu Paste Special to add the data as a new
series*. I repeated this with row 4 and row 7.
*You can also add the data as new points, which puts them into the
original
series.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"Don F." <Don wrote in message
...
I am trying to chart a series of data from multiple rows - ref the
following
table:
YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
1992 164278 164864 164221 165084 165920 166544 167284 168184 170158
170859
171531 173598
1993 175683 174121 172829 176710 178999 178772 180987 180996 182227
183233
185266 186538
1994 185948 188562 191756 192275 191300 193606 194229 196568 197876
200281
200301 201139
Can this be done without reformating the table? If so how?
Thanks in advance for your help!