PLEASE HELP! Set data range for chart based on date

What's your chart's SERIES() function?

One approach would be to enter the array formula,
{=OFFSET(A1,MONTH(TODAY())*5-5,,5)}, cells B1:B5,
and then use this cell reference in your SERIES() \
function.

I'm not exactly sure how to figure that out. When
I go to the source data for the chart, the data
range is not displayed because it is too complex.
the chart is simply a line graph with 2 lines. All
data is in columns.

Click on each of the lines, copy the SERIES() formula
from the formula bar and paste into a follow-up
posting.

Here are the two series formulas:

=SERIES('Delivery TO AZLP (Capsules)'!$B$4:$C$4,'Delivery TO AZLP (Capsules)'!$A$20:$A$23,'Delivery TO AZLP (Capsules)'!$C$20:$C$23,1)
and
=SERIES('Delivery TO AZLP (Capsules)'!$D$4:$E$4,'Delivery TO AZLP (Capsules)'!$A$20:$A$23,'Graph Data'!$AQ$19:$AQ$22,2)

Thanks for your help!

Greg, why is it that you're only charting 3
values. It was my understanding for your orignal
posting that your wanted to chart 5 values that
were dependent on the month?

It actually depends on how many weeks are in the
particular month. It will either be 4 or 5 weeks.
The graph is currently set for April, which has 4
weeks according to our dating system.

Where's the first value in...

'Delivery TO AZLP (Capsules)'!A:A
'Graph Data'!A:A

Hi Greg

I have not read all of what has been said, but on my Website I have some examples for charts under "Chart Tips and Tricks" and "Dynamic ranges" that may be of use to you.

...After you have looked at them you may be able to use a Dynamic range with this formula.

=OFFSET($A$1,0,0,MONTH(TODAY()),1)

This will atomatically expand down 1 row each month. so if the month was December it would cover the range: A1:A10

Hope this helps

Dave

OzGrid Business Applications

Stay with me Greg...

need answers to my questions at
14449.html

...we're almost there!

The first value in Delivery to AZLP (CAPSULES) is C7
The first value in Graph data is AQ6

Here we go!!

Greg, let's do this 1 step at a time beginning with
your 1st SERIES() function which currently looks
like:

=SERIES('Delivery TO AZLP (Capsules)'!$B$4:$C$4,'Delivery TO AZLP (Capsules)'!$A$20:$A$23,'Delivery TO AZLP (Capsules)'!$C$20:$C$23,1)

...when we're done it'll look like:

=SERIES('Delivery TO AZLP (Capsules)'!$B$4:$C$4,'Delivery TO AZLP (Capsules)'!X_Range1,'Delivery TO AZLP (Capsules)'!Y_Range1,1)

Before we make this change we'll need to create
the infrastructure to handle the 4,4,5 week mapping.
I assume that you want the month used by your chart
to be based on Excel's TODAY() function. Let me know
if that's not the case!

With 'Delivery TO AZLP (Capsules)' as your active
worksheet...

1. Create a globally defined name, Height, that refers to:
=CHOOSE(MONTH(TODAY()),4,4,5,4,4,5,4,4,5,4,4,5)

2. Create a globally defined name, Rows, that refers to:
=CHOOSE(MONTH(TODAY()),0,4,8,13,17,21,26,30,34,39,43,47)

Note: If you're wondering how these values were derived
each value is the cumulative sum of the 4,4,5's from
previous monthly periods. January is 0 because there are
no previous months. February is 4 because the January
'Height' value is 4. March is 8 because both January's and
February's 'Height' values are 4.

3. Create the locally defined name,
'Delivery TO AZLP (Capsules)'!X_Range1, that refers to:
=OFFSET('Delivery TO AZLP (Capsules)'!$A$7,Rows,,Height)

4. Create the locally defined name,
'Delivery TO AZLP (Capsules)'!Y_Range1, that refers to:
=OFFSET('Delivery TO AZLP (Capsules)'!X_Range1,,2)

5. Now you're ready to replace your chart's 1st SERIES()
formula with the new formulation. Click on the line
graph of the 1st series. You'll know that your selected
the proper line because the last argument to the
SERIES() function displayed in the formula bar will
be a 1. Paste the new formulation listed above into the
formula bar replacing the "old" formula.

Let's make sure this goes smoothly before we address
your chart's 2nd SERIES() function.

Formula contains an invalid external reference to a worksheet

Hey Mark,
Thanks for all of your help thus far. I folowed you instructions
to the T, but when I try to change the series formula
it keeps giving me the invalid external reference
error. Any ideas? I was cutting and pasting your
formula.

figured out part of the problem

It's the "Y_Range1" name that it is having a problem. Greg, let's do this 1 step at a time beginning with

Please disregard the next two posts, I figured it out

Works Great!!!! Greg, let's do this 1 step at a time beginning with

Now...

all we have to do is create a similiar OFFSET() for
the X-axis of your 2nd SERIES(). Can you figure it
out or shall we go for that one too? Works Great!!!! : Greg, let's do this 1 step at a time beginning with

Let me guess...

The SERIES() function is very finicky. If you
inadvertently created local defined names (i.e.,
not prefixed with the worksheet name like
'worksheet_name'!Y_Range1) it won't accept 'em.
This is a very subtle distinction. I suspect
that lots of users aren't familiar with this
aspect of defined names. It's the "Y_Range1" name that it is having a problem. : Greg, let's do this 1 step at a time beginning with

Thanx! I've got it now...

Thank you sooo much. I think I can handle it from
here. Thanks again Mark! all we have to do is create a similiar OFFSET() for