MrExcel Publishing
Your One Stop for Excel Tips & Solutions

PLEASE HELP! Set data range for chart based on date


Posted by Greg Vincent on April 11, 2001 1:05 PM

I have been trying to figure this out for about a week
now. I want my chart to adjust the range that it is
pulling data from based on whatever month it is. for
example, if it is January, I would like it to pull data
from a1 through a5. If it's February, then a6-a10, and
so on....

Thanks in advance!


Posted by Mark W. on April 11, 2001 1:30 PM

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

Posted by Mark W. on April 11, 2001 1:39 PM

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.

Posted by Greg on April 11, 2001 2:07 PM

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.

Posted by Mark W. on April 11, 2001 2:18 PM

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

Posted by Greg on April 12, 2001 5:33 AM

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!

Posted by Mark W. on April 12, 2001 6:54 AM

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?

Posted by Greg on April 12, 2001 10:04 AM

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.

Posted by Mark W. on April 12, 2001 11:01 AM

Where's the first value in...

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

Posted by Dave Hawley on April 12, 2001 11:04 AM

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

Posted by Mark W. on April 12, 2001 11:13 AM

Stay with me Greg...

need answers to my questions at
14449.html

...we're almost there!

Posted by Greg on April 13, 2001 5:48 AM

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

Posted by Mark W. on April 13, 2001 9:21 AM

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.

Posted by Greg on April 13, 2001 12:15 PM

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.

Posted by Greg on April 13, 2001 12:29 PM

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

Posted by Greg on April 13, 2001 12:36 PM

Please disregard the next two posts, I figured it out

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

Posted by Mark W. on April 13, 2001 12:49 PM

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

Posted by Mark W. on April 13, 2001 12:54 PM

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

Posted by Greg on April 13, 2001 12:59 PM

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