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
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|