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 |
#11
|
|||
|
|||
Automatically update graphs
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 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 |
#12
|
|||
|
|||
Automatically update graphs
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 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 |
#13
|
|||
|
|||
Automatically update graphs
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 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 |
#14
|
|||
|
|||
Automatically update graphs
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 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 |
#15
|
|||
|
|||
Automatically update graphs
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 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 |
#16
|
|||
|
|||
Automatically update graphs
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 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 |
#17
|
|||
|
|||
Automatically update graphs
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 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 |
#18
|
|||
|
|||
Automatically update graphs
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 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 |
#19
|
|||
|
|||
Automatically update graphs
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 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 |
#20
|
|||
|
|||
Automatically update graphs
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 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 | |
|
|