I have a strange problem which is different to explain.

I have a workbook with five sheets containing data and a cell with
a date in it. A sixth sheet contains a table with data collected
from the first five sheets, with the dates as categories, like
this;

08.2004 09.2004 10.2004 11.2004 12.2004
3 4 4 5 3
4 3 5 4 4
3 2 5 3 4


The table is used for generating a line diagram. Now, the diagram
looks fine when I only include data and categories from the first
four columns. If I include the last column, the diagram collapses
to one set of data points on top of each other, with a category
label of 38292 or something like it.

If I delete the last category (12.2004), the error goes away and a
proper line reappears. But then I lose the category label in the
diagram.

The error only appears when the category cells are formatted as
dates. If I format them as text, the line diagram is OK. The
trouble is that the five data sheets contain data which is
supposed to be imported via a macro, and the dates import in date
format, even if I've preformatted the cells they're copied into as
text. With some combinations of cell formats texts values (like
38292) appear in the diagram instead of dates.

I can't find a way to have Excel keep the dates in text format
when importing. And I can't for the life of me figure out why the
problem only appears when the last one of the five dates is added.

Does anybody understand what I mean here ...? And - please -
suggest a way to get around this??

If you type 38292 in a cell and format it as a Date, you'll see 11/01/2004 (Nov
1, 2004).

I suspect that some of your header values are dates and some are text.

If you want to make them text, you can start with an apostrophe:

'08.2004
or preformat the cell as text




Richard H wrote:

I have a strange problem which is different to explain.

I have a workbook with five sheets containing data and a cell with
a date in it. A sixth sheet contains a table with data collected
from the first five sheets, with the dates as categories, like
this;

08.2004 09.2004 10.2004 11.2004 12.2004
3 4 4 5 3
4 3 5 4 4
3 2 5 3 4

The table is used for generating a line diagram. Now, the diagram
looks fine when I only include data and categories from the first
four columns. If I include the last column, the diagram collapses
to one set of data points on top of each other, with a category
label of 38292 or something like it.

If I delete the last category (12.2004), the error goes away and a
proper line reappears. But then I lose the category label in the
diagram.

The error only appears when the category cells are formatted as
dates. If I format them as text, the line diagram is OK. The
trouble is that the five data sheets contain data which is
supposed to be imported via a macro, and the dates import in date
format, even if I've preformatted the cells they're copied into as
text. With some combinations of cell formats texts values (like
38292) appear in the diagram instead of dates.

I can't find a way to have Excel keep the dates in text format
when importing. And I can't for the life of me figure out why the
problem only appears when the last one of the five dates is added.

Does anybody understand what I mean here ...? And - please -
suggest a way to get around this??

--

Dave Peterson

Dave,

thank you for your response. I've investigated further and made
certain that all header values are formatted the same way now, like
this: m/yyyy.

But the problem didn't go away. What I've discovered is that the
collapse of the line diagram into one "column" happens when the more
than four data columns are added. It doesn't matter if the last one
is the column to the far right or far left or somewhere in between.
Everything is OK until the data series comprises more than four
columns.

I've also discovered that with a date formula like the one I'm using
now, the X axis "stretches" acroding to what dates I enter as
headers. If the first date is 1/1900 and the others are recent, the
first data points wind up at the extreme left of the diagram while
the others appear on top of each other at the extreme right. The
stacking of all points that I've described happens when the dates in
the header are all alike. So what I gather is that the date format is
creating the trouble, and that I should revert to text format to make
the X axis "static" again.

Now, the trouble is that when I import data into the five
workjsheets, the dates come over in date format ("dec.04" etc), even
though they're formatted as text in the original workbooks. I'm using
this VBA code to handle the import:

With ThisWorkbook.Sheets("Database1")
Set b = .Range("K4")
End With
With wb.Sheets("C Diagram")
Set a = .Range("B6")
End With
a.Formula = a.Value2
a.Copy b

So it seems that I need to amend the last two lines so that the value
is copied/pasted in text format.

Can this be done??

Regards,
Richard


Dave Peterson wrote in
:

If you type 38292 in a cell and format it as a Date, you'll see
11/01/2004 (Nov 1, 2004).

I suspect that some of your header values are dates and some are
text.

If you want to make them text, you can start with an apostrophe:

'08.2004
or preformat the cell as text

If you format all those header cells, what do you see.

If you see those date serial numbers for some, but not others, then you haven't
quite fixed the problem.

Just reformatting the cell won't help. You have to reenter the value, too.

And maybe something like:

With ThisWorkbook.Sheets("Database1")
Set b = .Range("K4")
End With
With wb.Sheets("C Diagram")
Set a = .Range("B6")
End With
with b
.numberformat = "@" 'text
.value = a.text
'or
.value = format(a,"mm.yyyy")
end with


====
When you're typing the contents of cells that you want treated as text, you can
either pre-format the cell as text ("@" in code) or prefix the entry with an
apostrophe (').

So
b.value = "'" & a.text
or
b.value = "'" & format(a,"mm.yyyy")

might work ok to (depending on what's in that "a" cell.)



Richard H wrote:

Dave,

thank you for your response. I've investigated further and made
certain that all header values are formatted the same way now, like
this: m/yyyy.

But the problem didn't go away. What I've discovered is that the
collapse of the line diagram into one "column" happens when the more
than four data columns are added. It doesn't matter if the last one
is the column to the far right or far left or somewhere in between.
Everything is OK until the data series comprises more than four
columns.

I've also discovered that with a date formula like the one I'm using
now, the X axis "stretches" acroding to what dates I enter as
headers. If the first date is 1/1900 and the others are recent, the
first data points wind up at the extreme left of the diagram while
the others appear on top of each other at the extreme right. The
stacking of all points that I've described happens when the dates in
the header are all alike. So what I gather is that the date format is
creating the trouble, and that I should revert to text format to make
the X axis "static" again.

Now, the trouble is that when I import data into the five
workjsheets, the dates come over in date format ("dec.04" etc), even
though they're formatted as text in the original workbooks. I'm using
this VBA code to handle the import:

With ThisWorkbook.Sheets("Database1")
Set b = .Range("K4")
End With
With wb.Sheets("C Diagram")
Set a = .Range("B6")
End With
a.Formula = a.Value2
a.Copy b

So it seems that I need to amend the last two lines so that the value
is copied/pasted in text format.

Can this be done??

Regards,
Richard

Dave Peterson wrote in
:

If you type 38292 in a cell and format it as a Date, you'll see
11/01/2004 (Nov 1, 2004).

I suspect that some of your header values are dates and some are
text.

If you want to make them text, you can start with an apostrophe:

'08.2004
or preformat the cell as text

--

Dave Peterson