Skipping Dates

Hello all,

I have a chart that is based on dates and I want to be able to skip dates
that do not have any data associated with it.

Example:

A B
1/1/05 1
1/2/05 2
1/3/05 1
1/6/05 3
1/7/05 2

Currently, my X axis has the dates 1-31 and if there is a gap in dates, it
plots the missing dates as zero. I want the chart to not not put the missing
dates from the range on the axis. In other words, just have 1, 2, 3, 6, 7,
etc on the X axis.

How do I do that? I have already tried changing the Chart options from
Automatic to Time-Scale .

Thanks for any help,

Sharon
Sharon

try
chart options>axis>category

--
Don Guillett
SalesAid Software
[email protected]
"Sharon" <[email protected]> wrote in message
news:[email protected]...
> Hello all,
>
> I have a chart that is based on dates and I want to be able to skip dates
> that do not have any data associated with it.
>
> Example:
>
> A B
> 1/1/05 1
> 1/2/05 2
> 1/3/05 1
> 1/6/05 3
> 1/7/05 2
>
> Currently, my X axis has the dates 1-31 and if there is a gap in dates, it
> plots the missing dates as zero. I want the chart to not not put the
missing
> dates from the range on the axis. In other words, just have 1, 2, 3, 6,
7,
> etc on the X axis.
>
> How do I do that? I have already tried changing the Chart options from
> Automatic to Time-Scale .
>
> Thanks for any help,
>
> Sharon
> Sharon

Okay, we're on the right track now. That worked, but since my series is
based on $BB$3:$BB$65 but I only have data from $BB$3:$BB$25 right now, it
wants to squish the first 25 lines into the first third of the chart and it
plots zero for the cells 26-65. Is there a way I can get it to only plot
where there is data? My data source ($BB$3:$BB$65) is a formula.

Thanks,

Sharon

"Don Guillett" wrote:

> try
> chart options>axis>category
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Sharon" <[email protected]> wrote in message
> news:[email protected]...
> > Hello all,
> >
> > I have a chart that is based on dates and I want to be able to skip dates
> > that do not have any data associated with it.
> >
> > Example:
> >
> > A B
> > 1/1/05 1
> > 1/2/05 2
> > 1/3/05 1
> > 1/6/05 3
> > 1/7/05 2
> >
> > Currently, my X axis has the dates 1-31 and if there is a gap in dates, it
> > plots the missing dates as zero. I want the chart to not not put the
> missing
> > dates from the range on the axis. In other words, just have 1, 2, 3, 6,
> 7,
> > etc on the X axis.
> >
> > How do I do that? I have already tried changing the Chart options from
> > Automatic to Time-Scale .
> >
> > Thanks for any help,
> >
> > Sharon
> > Sharon
>
>
>

Sharon -

Use a dynamic range, which grows as data is added to the range. Here are
some examples and links:

http://peltiertech.com/Excel/Charts/Dynamics.html

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


Sharon wrote:

> Okay, we're on the right track now. That worked, but since my series is
> based on $BB$3:$BB$65 but I only have data from $BB$3:$BB$25 right now, it
> wants to squish the first 25 lines into the first third of the chart and it
> plots zero for the cells 26-65. Is there a way I can get it to only plot
> where there is data? My data source ($BB$3:$BB$65) is a formula.
>
> Thanks,
>
> Sharon
>
> "Don Guillett" wrote:
>
>
>>try
>>chart options>axis>category
>>
>>--
>>Don Guillett
>>SalesAid Software
>>[email protected]
>>"Sharon" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>Hello all,
>>>
>>>I have a chart that is based on dates and I want to be able to skip dates
>>>that do not have any data associated with it.
>>>
>>>Example:
>>>
>>>A B
>>>1/1/05 1
>>>1/2/05 2
>>>1/3/05 1
>>>1/6/05 3
>>>1/7/05 2
>>>
>>>Currently, my X axis has the dates 1-31 and if there is a gap in dates, it
>>>plots the missing dates as zero. I want the chart to not not put the
>>
>>missing
>>
>>>dates from the range on the axis. In other words, just have 1, 2, 3, 6,
>>
>>7,
>>
>>>etc on the X axis.
>>>
>>>How do I do that? I have already tried changing the Chart options from
>>>Automatic to Time-Scale .
>>>
>>>Thanks for any help,
>>>
>>>Sharon
>>>Sharon
>>
>>
>>

I tried that and it seems like it is what I need to do, but when I try to
enter the data source it gives me an error message saying that, "The
reference is not valid. References for titles, values or sizes must be a
single cell, row or column."

My headers consist of rows 1-3 and my data starts on row 4. Cells A1:C2 are
merged to create a title and cells A3, B3 and C3 are the headers for the data.

I modified the formulas on your website to suit my data as follows:

=OFFSET(RYGSeries1ChartValues, 0, -4)

=OFFSET(Sheet1!$J$4,4,0,COUNTA(Sheet1!$J:$J)-4,4)

=OFFSET(Sheet1!$N$4,4,0,COUNTA(Sheet1!$N:$N)-4,4)

Do you know what I'm doing wrong here?

Thanks so much,

Sharon

"Jon Peltier" wrote:

> Sharon -
>
> Use a dynamic range, which grows as data is added to the range. Here are
> some examples and links:
>
> http://peltiertech.com/Excel/Charts/Dynamics.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Sharon wrote:
>
> > Okay, we're on the right track now. That worked, but since my series is
> > based on $BB$3:$BB$65 but I only have data from $BB$3:$BB$25 right now, it
> > wants to squish the first 25 lines into the first third of the chart and it
> > plots zero for the cells 26-65. Is there a way I can get it to only plot
> > where there is data? My data source ($BB$3:$BB$65) is a formula.
> >
> > Thanks,
> >
> > Sharon
> >
> > "Don Guillett" wrote:
> >
> >
> >>try
> >>chart options>axis>category
> >>
> >>--
> >>Don Guillett
> >>SalesAid Software
> >>[email protected]
> >>"Sharon" <[email protected]> wrote in message
> >>news:[email protected]...
> >>
> >>>Hello all,
> >>>
> >>>I have a chart that is based on dates and I want to be able to skip dates
> >>>that do not have any data associated with it.
> >>>
> >>>Example:
> >>>
> >>>A B
> >>>1/1/05 1
> >>>1/2/05 2
> >>>1/3/05 1
> >>>1/6/05 3
> >>>1/7/05 2
> >>>
> >>>Currently, my X axis has the dates 1-31 and if there is a gap in dates, it
> >>>plots the missing dates as zero. I want the chart to not not put the
> >>
> >>missing
> >>
> >>>dates from the range on the axis. In other words, just have 1, 2, 3, 6,
> >>
> >>7,
> >>
> >>>etc on the X axis.
> >>>
> >>>How do I do that? I have already tried changing the Chart options from
> >>>Automatic to Time-Scale .
> >>>
> >>>Thanks for any help,
> >>>
> >>>Sharon
> >>>Sharon
> >>
> >>
> >>
>

You may have answered it:

> "The reference is not valid. References for titles, values or sizes
> must be a single cell, row or column."

> =OFFSET(Sheet1!$J$4,4,0,COUNTA(Sheet1!$J:$J)-4,4)
> =OFFSET(Sheet1!$N$4,4,0,COUNTA(Sheet1!$N:$N)-4,4)

These two formulas refer to ranges 4 columns wide (the last 4 within the
parens).
/sig
Sharon wrote:
> I tried that and it seems like it is what I need to do, but when I try to
> enter the data source it gives me an error message saying that, "The
> reference is not valid. References for titles, values or sizes must be a
> single cell, row or column."
>
> My headers consist of rows 1-3 and my data starts on row 4. Cells A1:C2 are
> merged to create a title and cells A3, B3 and C3 are the headers for the data.
>
> I modified the formulas on your website to suit my data as follows:
>
> =OFFSET(RYGSeries1ChartValues, 0, -4)
>
> =OFFSET(Sheet1!$J$4,4,0,COUNTA(Sheet1!$J:$J)-4,4)
>
> =OFFSET(Sheet1!$N$4,4,0,COUNTA(Sheet1!$N:$N)-4,4)
>
> Do you know what I'm doing wrong here?
>
> Thanks so much,
>
> Sharon
>
> "Jon Peltier" wrote:
>
>
>>Sharon -
>>
>>Use a dynamic range, which grows as data is added to the range. Here are
>>some examples and links:
>>
>> http://peltiertech.com/Excel/Charts/Dynamics.html
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>Sharon wrote:
>>
>>
>>>Okay, we're on the right track now. That worked, but since my series is
>>>based on $BB$3:$BB$65 but I only have data from $BB$3:$BB$25 right now, it
>>>wants to squish the first 25 lines into the first third of the chart and it
>>>plots zero for the cells 26-65. Is there a way I can get it to only plot
>>>where there is data? My data source ($BB$3:$BB$65) is a formula.
>>>
>>>Thanks,
>>>
>>>Sharon
>>>
>>>"Don Guillett" wrote:
>>>
>>>
>>>
>>>>try
>>>>chart options>axis>category
>>>>
>>>>--
>>>>Don Guillett
>>>>SalesAid Software
>>>>[email protected]
>>>>"Sharon" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>
>>>>>Hello all,
>>>>>
>>>>>I have a chart that is based on dates and I want to be able to skip dates
>>>>>that do not have any data associated with it.
>>>>>
>>>>>Example:
>>>>>
>>>>>A B
>>>>>1/1/05 1
>>>>>1/2/05 2
>>>>>1/3/05 1
>>>>>1/6/05 3
>>>>>1/7/05 2
>>>>>
>>>>>Currently, my X axis has the dates 1-31 and if there is a gap in dates, it
>>>>>plots the missing dates as zero. I want the chart to not not put the
>>>>
>>>>missing
>>>>
>>>>
>>>>>dates from the range on the axis. In other words, just have 1, 2, 3, 6,
>>>>
>>>>7,
>>>>
>>>>
>>>>>etc on the X axis.
>>>>>
>>>>>How do I do that? I have already tried changing the Chart options from
>>>>>Automatic to Time-Scale .
>>>>>
>>>>>Thanks for any help,
>>>>>
>>>>>Sharon
>>>>>Sharon
>>>>
>>>>
>>>>