Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 1st 07, 12:32 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 18
Default Automatically update graphs

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

  #2   Report Post  
Old February 1st 07, 02:51 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Automatically update graphs

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



  #3   Report Post  
Old February 1st 07, 10:35 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 18
Default Automatically update graphs

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




  #4   Report Post  
Old February 2nd 07, 02:51 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 18
Default Automatically update graphs

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




  #5   Report Post  
Old February 2nd 07, 02:55 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Automatically update graphs

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
news
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








  #6   Report Post  
Old February 2nd 07, 03:10 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Automatically update graphs

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





  #7   Report Post  
Old February 2nd 07, 03:22 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 18
Default Automatically update graphs

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
news
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






  #8   Report Post  
Old February 2nd 07, 03:21 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,071
Default Automatically update graphs

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
news
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






  #9   Report Post  
Old February 2nd 07, 08:52 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Automatically update graphs

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
news
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








  #10   Report Post  
Old February 3rd 07, 12:01 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 18
Default Automatically update graphs

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
news 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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i automatically update % figures daily JohnM Excel Discussion (Misc queries) 5 September 11th 06 05:52 PM
Linked cells don't automatically update in Excel 2003. Why? jrwrm Excel Worksheet Functions 1 August 31st 06 10:51 PM
how do i update a chart area automatically Kay Charts and Charting in Excel 4 February 10th 06 09:10 PM
How do i update hyperlink in excel spread sheet automatically. Phanichand Mudumba Excel Discussion (Misc queries) 1 September 13th 05 05:46 PM
Update multiple workbooks automatically golfer Excel Worksheet Functions 1 December 23rd 04 09:39 PM


All times are GMT +1. The time now is 07:44 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017