Predetermine Scale of Chart by Cell Entry

Posted by Jake Moore on November 21, 2001 6:44 AM

Hello everyone,

I'm looking to be able to streamline data by entering in a 'begining' and 'ending' date for my data. I'm looking for a way to have a chart change it's scale to the values that I've put into a cell.

Say my data ranges over a year long period, Jan, Feb, March, etc.

So, I'd like to put into two cells the begining date and end date and JUST look at that range.

For instance, one case might be:
'Begining' = Jan 1
'Ending' = Feb 1

Another might be:

'Begining' = Feb 1
'Ending' = Nov 1

Another might be:

'Begining' = Jun 1
'Ending' = July 1

I'd like to be able to modify the chart by simply changing two cell values!

Any and all help is GREATLY appreciated!

Posted by Mark W. on November 21, 2001 7:23 AM

This can be easily accomplished using named ranges.
Suppose that cells Sheet1!A1:B1 contains
{"Beginning","Ending"} and cells Sheet1!A2:B2
contain date values for Jan 1st and Jun 1st
respectively. Futhermore, cells Sheet1!A4:B16
contains...

Date Value
Jan 1 10
Feb 1 20
Mar 1 30
Apr 1 40
May 1 50
Jun 1 60
Jul 1 70
Aug 1 80
Sep 1 90
Oct 1 100
Nov 1 110
Dec 1 120

1. Create the following Defined Names...

'Beginning' refers to =Sheet1!A2
'Ending' refers to =Sheet1!B2
'Date' refers to =Sheet1!A5:A16
'X_Value' refers to =INDEX(Date,MATCH(Beginning,Date,0)):INDEX(Date,MATCH(Ending,Date,0))
'Y_Value' refers to =OFFSET(X_Range,0,1)

After creating a chart using Sheet1!A4:B16 as
it's source data modify the chart's SERIES
function from...

=SERIES(Sheet1!$B$4,Sheet1!$A$5:$A$16,Sheet1!$B$5:$B$16,1)

...to =SERIES(Sheet1!$B$4,Sheet1!X_Range,Sheet1!Y_Range,1)

Now you can change your 'Beginning' and 'Ending'
date values and your chart will respond accordingly.

Posted by Jake Moore (again) on November 21, 2001 8:04 AM

Hey Mark,

Thanks for the input, HOWEVER, what do you mean by 'Defined Name'? I'm sorry if this is a newbie question, (I'm pretty good w/ Excel, and what you're refering to is probably what I've done a million times, but I just don't realize it!)

Thanks for the great help thus far!

This can be easily accomplished using named ranges.

Posted by Jake on November 21, 2001 8:06 AM

Sorry, I figured out 'Defined Names'

SORRY GUYS,

I figured it out!!!!!!!!!