View Single Post
  #23   Report Post  
Posted to microsoft.public.excel.charting
Jon Peltier Jon Peltier is offline
external usenet poster
 
Posts: 6,582
Default Automatically update graphs

Assuming AllDates is highlighted...

What are StartDate and EndDate?

What do you get if you enter
=MATCH(StartDate,AllDates,1)
and
=MATCH(EndDate,AllDates,1)
into cells in the worksheet?

Instead of the OFFSET refers-to formula for ChartDates, try this:
=INDEX(AllDates,MATCH(StartDate,AllDates,1)-1):INDEX(AllDates,MATCH(EndDate,AllDates,1))

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


"ChrisG" wrote in message
...
Thank you for that fix. Now what do we do with regards to the fact that
ChartDates
("=OFFSET(AllDates,MATCH(StartDate,AllDates,1)-1,0,MATCH(EndDate,AllDates,1)-MATCH(StartDate,AllDates,1)+1,1)")
is not highlighted, and neither is ChartEuropeanEquity
("=OFFSET(ChartDates,0,1)") ?

"Jon Peltier" wrote:

The problem you described is with the first part if you insert rows above
A9. A simple change would be:

=INDEX('Raw Data'!$A:$A,9):INDEX('Raw Data'!$A:$A,MATCH(9.99999E+307,'Raw
Data'!$A:$A))

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


"ChrisG" wrote in message
...
AllDates refers to "='Raw Data'!$A$9:INDEX('Raw
Data'!$A:$A,MATCH(9.99999E+307,'Raw Data'!$A:$A))" How should we do
this
so
that it is easier to follow?

"Jon Peltier" wrote:

How is AllDates defined? This thread has gotten a bit long to follow.

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


"ChrisG" wrote in message
...
I'm not sure what you mean by that. Is that for my "AllDates"
problem
or
the
problem with my ChartDates and ChartEuropean Equity? None of my
defined
names have a formula like that. See my above post to see exactly
what
I
have
entered. I have followed your Dynamic Charting By Dates article
(TechTrax
Article) exactly if that helps.

Chris

"Jon Peltier" wrote:

Start your offset from higher:

=OFFSET(Sheet1!$A$8,0,0)

is the same as

=OFFSET(Sheet!$A$1,7,0)

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


"ChrisG" wrote in message
...
Hi Jon, The "All Dates" is highlighted, however I noticed that
because
I
insert a row into row 8 every day, instead of reading "='Raw
Data'!$A$8...."
it read ""='Raw Data'!$A$10...." is there any way to get it to
stay
on
row
8
forever? The rest of the defined names (excluding Start and End
Dates)
did
not highlight any section of the worksheet. Hopefully this will
give
you
some idea what is going on!

"Jon Peltier" wrote:

In the define names dialog, select one of these names, and click
in
the
Refers To box. Is the expected range highlighted?

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


"ChrisG" wrote in message
...
Jon, those mistakes were merely type-o's that I made
re-entering
it
in
this
window. I have entered "=Series('Raw Data'!$B$1,'Raw
Data'!ChartDates,'Raw
Data'!ChartEuropeanEquity,1)" in the values box, and I have
defined
ChartDates as
"=OFFSET(AllDates,MATCH(StartDate,AllDates,1)-1,0,MATCH(EndDate,AllDates,1)-MATCH(StartDate,AllDates,1)+1,1)"
and I have defined ChartEuropeanEquity as
"=OFFSET(ChartDates,0,1)".
AllDates is defined as "='Raw Data'!$A$5:INDEX('Raw
Data'!$A:$A,MATCH(9.99999E+307,Data!$A:$A))" and Start and End
dates
are
defined as "='Raw Data'!$Q$2" and "='Raw Data'!$Q$3"
respectively.
My
titles
of the different columns are in row 1 and my data does not
begin
until
row
8.
I could really use your expertise in figuring out what is
wrong.
Thanks.




"Jon Peltier" wrote:

You have one single quote and one double quote around the
first
'Raw
Data',
and the third is spelled without a space.

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


"ChrisG" wrote in message
...
Hey Jon,
I tried copying your generic "values" box formula
"=SERIES('My
Sheet'!$B$1,'My Sheet'!MyXValues,'My Sheet'!MyYValues,1)"
So I entered "=Series('Raw Data"!$B$1,'Raw
Data'!ChartDates,'RawData'!ChartEuropeanEquity,1)" however
I
am
still
told
there is an error in the formula. I know this must be
frustrating
for
you,
but believe me, it is just as frustrating, if not more so
for
me!
Thanks
again,
Chris

"Jon Peltier" wrote:

What I said was, I start with a static chart with a series
formula
that
points to static ranges, then I edit the series formula so
it
points
to
the
dynamic named ranges.

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


"ChrisG" wrote in
message
...
I'll be honest, that confused me! I have been following
the
article
I
mentioned before to a tee, but now I am confused as to
what
formula
I
should
put in for the values box. Where do I put in the "'Raw
Data'ChartEuropeanEquity"? I want to create a dynamic
chart,
and
with
=SERIES('My Sheet'!$B$1,'My Sheet'!$A$2:$A$18,'My
Sheet'!$B$2:$B$18,1)
wouldn't that create a static sheet? Sorry I am so
lost, I
really
apprecitate your help
Chris

"Jon Peltier" wrote:

Make sure the worksheet name and the defined name are
correctly
referenced
in the formula. Sometimes what I do is create the chart
with
data
from
the
worksheet on which the defined names reside, then
change
the
addresses
in
the chart formula to the defined names; this is
relatively
foolproof.
This
means change this:

=SERIES('My Sheet'!$B$1,'My Sheet'!$A$2:$A$18,'My
Sheet'!$B$2:$B$18,1)

to this:

=SERIES('My Sheet'!$B$1,'My Sheet'!MyXValues,'My
Sheet'!MyYValues,1)

When you hit Enter, Excel changes the sheet name to the
workbook
name
if
the
names are defined for the workbook at large:

=SERIES('My
Sheet'!$B$1,TestBook.xls!MyXValues,TestBook.xls!My YValues,1)

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


"ChrisG" wrote in
message
...
Thanks for your help Jon, I am using the "Dynamic
Charting
By
Dates
(TechTrax
Article)" However, when I am actuallty creating the
chart,
when I
enter
"=Data!ChartFirmA" (except I have a different name
besides
"FirmA")
I
get
an
error message that says "Reference is not valid.
Reference
must
be
to
an
open worksheet." What does this mean, what did I do
wrong,
and
how
can
I
correct it? Thanks for your help,
Chris

"Jon Peltier" wrote:

See Dynamic Charts:


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

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