If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|