I have a worksheet full of raw data and other worksheets which are just
charts based on the raw data. I continually add more data to the raw data
worksheet, and I was wondering if there is a way to change the source data of
the chart so that it will automatically update if the raw data changes. To
clarify this, I will give an example. If on Monday I have 5 rows of data and
every day that week I add more rows, so that by friday I have 10 rows of data
(with the most recent data comprising the top row), can I have the chart
automatically include all of the data? Thanks for your help

See Dynamic Charts:

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

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


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are just
charts based on the raw data. I continually add more data to the raw data
worksheet, and I was wondering if there is a way to change the source data
of
the chart so that it will automatically update if the raw data changes.
To
clarify this, I will give an example. If on Monday I have 5 rows of data
and
every day that week I add more rows, so that by friday I have 10 rows of
data
(with the most recent data comprising the top row), can I have the chart
automatically include all of the data? Thanks for your help

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are just
charts based on the raw data. I continually add more data to the raw data
worksheet, and I was wondering if there is a way to change the source data
of
the chart so that it will automatically update if the raw data changes.
To
clarify this, I will give an example. If on Monday I have 5 rows of data
and
every day that week I add more rows, so that by friday I have 10 rows of
data
(with the most recent data comprising the top row), can I have the chart
automatically include all of the data? Thanks for your help

OK, I think i have the answer to that question, but I am still having
problems. My worksheet is called "Raw Data." But when I click on an empty
cell to create my graph, I enter "='Raw Data'!$B$1" into the name box, but
when I enter "='Raw Data'!ChartEuropeanEquity" I get a message that says
"Your formula contains an invalid external reference to a worksheet. Verify
that the path, workbook and range name or cell reference are correct, and try
again." What is wrong with the formula that I have written? Thanks for your
help,
Chris


"ChrisG" wrote:

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are just
charts based on the raw data. I continually add more data to the raw data
worksheet, and I was wondering if there is a way to change the source data
of
the chart so that it will automatically update if the raw data changes.
To
clarify this, I will give an example. If on Monday I have 5 rows of data
and
every day that week I add more rows, so that by friday I have 10 rows of
data
(with the most recent data comprising the top row), can I have the chart
automatically include all of the data? Thanks for your help

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are just
charts based on the raw data. I continually add more data to the raw
data
worksheet, and I was wondering if there is a way to change the source
data
of
the chart so that it will automatically update if the raw data changes.
To
clarify this, I will give an example. If on Monday I have 5 rows of
data
and
every day that week I add more rows, so that by friday I have 10 rows
of
data
(with the most recent data comprising the top row), can I have the
chart
automatically include all of the data? Thanks for your help

Try replacing 'Raw Data' with the workbook name. If the name resides on a
different sheet, then 'Raw Data'!BlahBlah may raise the error.

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


"ChrisG" wrote in message
...
OK, I think i have the answer to that question, but I am still having
problems. My worksheet is called "Raw Data." But when I click on an
empty
cell to create my graph, I enter "='Raw Data'!$B$1" into the name box, but
when I enter "='Raw Data'!ChartEuropeanEquity" I get a message that says
"Your formula contains an invalid external reference to a worksheet.
Verify
that the path, workbook and range name or cell reference are correct, and
try
again." What is wrong with the formula that I have written? Thanks for
your
help,
Chris


"ChrisG" wrote:

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are
just
charts based on the raw data. I continually add more data to the raw
data
worksheet, and I was wondering if there is a way to change the source
data
of
the chart so that it will automatically update if the raw data
changes.
To
clarify this, I will give an example. If on Monday I have 5 rows of
data
and
every day that week I add more rows, so that by friday I have 10 rows
of
data
(with the most recent data comprising the top row), can I have the
chart
automatically include all of the data? Thanks for your help

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are just
charts based on the raw data. I continually add more data to the raw
data
worksheet, and I was wondering if there is a way to change the source
data
of
the chart so that it will automatically update if the raw data changes.
To
clarify this, I will give an example. If on Monday I have 5 rows of
data
and
every day that week I add more rows, so that by friday I have 10 rows
of
data
(with the most recent data comprising the top row), can I have the
chart
automatically include all of the data? Thanks for your help

The first 2 links in the first paragraph in
Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html

are about 'named formulas' and 'using them in charts.' You may find both
links useful.
--
Regards,

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach


"ChrisG" wrote:

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are just
charts based on the raw data. I continually add more data to the raw
data
worksheet, and I was wondering if there is a way to change the source
data
of
the chart so that it will automatically update if the raw data changes.
To
clarify this, I will give an example. If on Monday I have 5 rows of
data
and
every day that week I add more rows, so that by friday I have 10 rows
of
data
(with the most recent data comprising the top row), can I have the
chart
automatically include all of the data? Thanks for your help

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are
just
charts based on the raw data. I continually add more data to the
raw
data
worksheet, and I was wondering if there is a way to change the
source
data
of
the chart so that it will automatically update if the raw data
changes.
To
clarify this, I will give an example. If on Monday I have 5 rows of
data
and
every day that week I add more rows, so that by friday I have 10
rows
of
data
(with the most recent data comprising the top row), can I have the
chart
automatically include all of the data? Thanks for your help

So by now I'm sure you know that I am no expert with regards to excel. One
thing you mentioned Tushar was that there can not be any breaks in between
the Column title and the actual data. My titles are in row 1 (B1 to N1)
however my data begins in row 8. I don't know whether I should be using the
Offset function or the series function (I told you i'm no expert!), and I am
unsure of what I should put in the Values box when I am trying to create my
graph. I have been following Jon's "Dynamic Charting By Dates (TechTrax
Article)" If my worksheet name is 'Raw Data' could you tell me what I should
put in that box? Thanks again, I really appreicaite it

"Tushar Mehta" wrote:

The first 2 links in the first paragraph in
Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html

are about 'named formulas' and 'using them in charts.' You may find both
links useful.
--
Regards,

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach


"ChrisG" wrote:

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are just
charts based on the raw data. I continually add more data to the raw
data
worksheet, and I was wondering if there is a way to change the source
data
of
the chart so that it will automatically update if the raw data changes.
To
clarify this, I will give an example. If on Monday I have 5 rows of
data
and
every day that week I add more rows, so that by friday I have 10 rows
of
data
(with the most recent data comprising the top row), can I have the
chart
automatically include all of the data? Thanks for your help

Hey Jon, Do you think you could help me out with the post I added under
Tushar? Thanks
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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are
just
charts based on the raw data. I continually add more data to the
raw
data
worksheet, and I was wondering if there is a way to change the
source
data
of
the chart so that it will automatically update if the raw data
changes.
To
clarify this, I will give an example. If on Monday I have 5 rows of
data
and
every day that week I add more rows, so that by friday I have 10
rows
of
data
(with the most recent data comprising the top row), can I have the
chart
automatically include all of the data? Thanks for your help

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are
just
charts based on the raw data. I continually add more data to the
raw
data
worksheet, and I was wondering if there is a way to change the
source
data
of
the chart so that it will automatically update if the raw data
changes.
To
clarify this, I will give an example. If on Monday I have 5 rows of
data
and
every day that week I add more rows, so that by friday I have 10
rows
of
data
(with the most recent data comprising the top row), can I have the
chart
automatically include all of the data? Thanks for your help

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are
just
charts based on the raw data. I continually add more data to the
raw
data
worksheet, and I was wondering if there is a way to change the
source
data
of
the chart so that it will automatically update if the raw data
changes.
To
clarify this, I will give an example. If on Monday I have 5 rows
of
data
and
every day that week I add more rows, so that by friday I have 10
rows
of
data
(with the most recent data comprising the top row), can I have
the
chart
automatically include all of the data? Thanks for your help

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which are
just
charts based on the raw data. I continually add more data to the
raw
data
worksheet, and I was wondering if there is a way to change the
source
data
of
the chart so that it will automatically update if the raw data
changes.
To
clarify this, I will give an example. If on Monday I have 5 rows
of
data
and
every day that week I add more rows, so that by friday I have 10
rows
of
data
(with the most recent data comprising the top row), can I have
the
chart
automatically include all of the data? Thanks for your help

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which
are
just
charts based on the raw data. I continually add more data to
the
raw
data
worksheet, and I was wondering if there is a way to change the
source
data
of
the chart so that it will automatically update if the raw data
changes.
To
clarify this, I will give an example. If on Monday I have 5
rows
of
data
and
every day that week I add more rows, so that by friday I have
10
rows
of
data
(with the most recent data comprising the top row), can I have
the
chart
automatically include all of the data? Thanks for your help

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets which
are
just
charts based on the raw data. I continually add more data to
the
raw
data
worksheet, and I was wondering if there is a way to change the
source
data
of
the chart so that it will automatically update if the raw data
changes.
To
clarify this, I will give an example. If on Monday I have 5
rows
of
data
and
every day that week I add more rows, so that by friday I have
10
rows
of
data
(with the most recent data comprising the top row), can I have
the
chart
automatically include all of the data? Thanks for your help

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets
which
are
just
charts based on the raw data. I continually add more data
to
the
raw
data
worksheet, and I was wondering if there is a way to change
the
source
data
of
the chart so that it will automatically update if the raw
data
changes.
To
clarify this, I will give an example. If on Monday I have
5
rows
of
data
and
every day that week I add more rows, so that by friday I
have
10
rows
of
data
(with the most recent data comprising the top row), can I
have
the
chart
automatically include all of the data? Thanks for your
help

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
_______


"ChrisG" wrote in message
...
I have a worksheet full of raw data and other worksheets
which
are
just
charts based on the raw data. I continually add more data
to
the
raw
data
worksheet, and I was wondering if there is a way to change
the
source
data
of
the chart so that it will automatically update if the raw
data
changes.
To
clarify this, I will give an example. If on Monday I have
5
rows
of
data
and
every day that week I add more rows, so that by friday I
have
10
rows
of
data
(with the most recent data comprising the top row), can I
have
the
chart
automatically include all of the data? Thanks for your
help

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
_______


"ChrisG" wrote in
message
...
I have a worksheet full of raw data and other worksheets
which
are
just
charts based on the raw data. I continually add more
data
to
the
raw
data
worksheet, and I was wondering if there is a way to
change
the
source
data
of
the chart so that it will automatically update if the
raw
data
changes.
To
clarify this, I will give an example. If on Monday I
have
5
rows
of
data
and
every day that week I add more rows, so that by friday I
have
10
rows
of
data
(with the most recent data comprising the top row), can
I
have
the
chart
automatically include all of the data? Thanks for your
help

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
_______


"ChrisG" wrote in
message
...
I have a worksheet full of raw data and other worksheets
which
are
just
charts based on the raw data. I continually add more
data
to
the
raw
data
worksheet, and I was wondering if there is a way to
change
the
source
data
of
the chart so that it will automatically update if the
raw
data
changes.
To
clarify this, I will give an example. If on Monday I
have
5
rows
of
data
and
every day that week I add more rows, so that by friday I
have
10
rows
of
data
(with the most recent data comprising the top row), can
I
have
the
chart
automatically include all of the data? Thanks for your
help

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
_______


"ChrisG" wrote in
message
...
I have a worksheet full of raw data and other
worksheets
which
are
just
charts based on the raw data. I continually add more
data
to
the
raw
data
worksheet, and I was wondering if there is a way to
change
the
source
data
of
the chart so that it will automatically update if the
raw
data
changes.
To
clarify this, I will give an example. If on Monday I
have
5
rows
of
data
and
every day that week I add more rows, so that by
friday I
have
10
rows
of
data
(with the most recent data comprising the top row),
can
I
have
the
chart
automatically include all of the data? Thanks for
your
help

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
_______

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
_______

All Dates is highlighted, and when I enter "=MATCH(StartDate,AllDates,1)"
into an empty cell in the worksheet, the number 290 pops up, same thing with
"=MATCH(EndDate,AllDates,1)" My StartDate is "=INDIRECT("A"&8)" and my
EndDate is "18/01/2007" (there is no formula). When I enter
"=INDEX(AllDates,MATCH(StartDate,AllDates,1)-1):INDEX(AllDates,MATCH(EndDate,AllDates,1))"
into the refers to box for ChartDates, it only highlights the first and
second dates of my data set (they are the furthest down the row).

"Jon Peltier" wrote:

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
_______

Chris -

I can't help any more by remote control. Send a copy of your workbook to
jonxlmvp (at) peltiertech (dot) com

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


"ChrisG" wrote in message
...
All Dates is highlighted, and when I enter "=MATCH(StartDate,AllDates,1)"
into an empty cell in the worksheet, the number 290 pops up, same thing
with
"=MATCH(EndDate,AllDates,1)" My StartDate is "=INDIRECT("A"&8)" and my
EndDate is "18/01/2007" (there is no formula). When I enter
"=INDEX(AllDates,MATCH(StartDate,AllDates,1)-1):INDEX(AllDates,MATCH(EndDate,AllDates,1))"
into the refers to box for ChartDates, it only highlights the first and
second dates of my data set (they are the furthest down the row).

"Jon Peltier" wrote:

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
_______

Upon inspection of Chris' workbook, I realized that his dates appeared in
descending order. The proper form for the MATCH function in this case is

MATCH(match_value, match_array, -1)

This cleared up his problem.

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


"Jon Peltier" wrote in message
...
Chris -

I can't help any more by remote control. Send a copy of your workbook to
jonxlmvp (at) peltiertech (dot) com

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


"ChrisG" wrote in message
...
All Dates is highlighted, and when I enter "=MATCH(StartDate,AllDates,1)"
into an empty cell in the worksheet, the number 290 pops up, same thing
with
"=MATCH(EndDate,AllDates,1)" My StartDate is "=INDIRECT("A"&8)" and my
EndDate is "18/01/2007" (there is no formula). When I enter
"=INDEX(AllDates,MATCH(StartDate,AllDates,1)-1):INDEX(AllDates,MATCH(EndDate,AllDates,1))"
into the refers to box for ChartDates, it only highlights the first and
second dates of my data set (they are the furthest down the row).

"Jon Peltier" wrote:

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
_______