Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
Hi,
I have a chart which relies on the data in a table with 29 rows. Occaisionally, I want to only use 28 rows as my data set for the chart. I can manually enter a named range into the dialog, and upon entry, it automatically ,morphs it into the string that describes the data range (worksheetname, data set). When I manually type in the other named range, it flips to the shorter named range in the table. How can I inject that into the chart's data dialog on the fly? I have tried to reference cell locations that have the named range name in them. No worky. I know it is possible to pass this value to the chart layout, but I do not know how. Anyone? |
#2
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
This post has some VBA techniques near the end:
http://peltiertech.com/WordPress/dyn...t-source-data/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "FatBytestard" wrote in message ... Hi, I have a chart which relies on the data in a table with 29 rows. Occaisionally, I want to only use 28 rows as my data set for the chart. I can manually enter a named range into the dialog, and upon entry, it automatically ,morphs it into the string that describes the data range (worksheetname, data set). When I manually type in the other named range, it flips to the shorter named range in the table. How can I inject that into the chart's data dialog on the fly? I have tried to reference cell locations that have the named range name in them. No worky. I know it is possible to pass this value to the chart layout, but I do not know how. Anyone? |
#3
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On Thu, 21 May 2009 23:40:15 -0400, "Jon Peltier"
wrote: This post has some VBA techniques near the end: http://peltiertech.com/WordPress/dyn...t-source-data/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ So you are saying that this is the best way to handle say leap year? I was wanting to get along without any VB code. Oh well. The data table for the 29th day would only receive data if there were a day 29, a leap year. So I leave the table at 29 rows, and only select 28 rows for the chart. On leap year, I want to select 29 rows as the data set referred to by the chart. All my other months' days keep track with whatever year is being done, and February does too. Just not the chart. I may have to explicitly call the sheet name, not merely the range name I declared. I will try that, and get back to you. Your stuff is nice, but I don't think I actually need a dynamic table to accommodate one single additional row every fourth year. :-) The table is fine. The chart needs to be able to be dynamically defined by accepting named ranges as data range input. That I can switch in once every fourth year. I mean I know I can make a separate chart and refer to that chart on leap year and the other during the interim, but is that the most elegant solution? Can I merely make another chart below the first February chart, and make it a 29 day reference, and I guessI don't have to worry about the year, since one 365th tick not filled would be barely noticeable. Seems like a kludge for me to simply add a leap year chart on my February Plot sheet, such that any monkey can tell which set of dual data is valid for the year they are in. Hmmm... divisible by 4, by 100, by 400... What's all this leap year crud?! |
#4
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
If you can use a defined name for the X and Y values for each series in the
chart, you can build dynamic charts that don't use VBA. Somehow when I read your description, I assumed you didn't want to do that. The defined names only work for the individual X and Y values, not for the total source data range. For info on creating dynamic charts: http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/WordPress/dynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "FatBytestard" wrote in message ... On Thu, 21 May 2009 23:40:15 -0400, "Jon Peltier" wrote: This post has some VBA techniques near the end: http://peltiertech.com/WordPress/dyn...t-source-data/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ So you are saying that this is the best way to handle say leap year? I was wanting to get along without any VB code. Oh well. The data table for the 29th day would only receive data if there were a day 29, a leap year. So I leave the table at 29 rows, and only select 28 rows for the chart. On leap year, I want to select 29 rows as the data set referred to by the chart. All my other months' days keep track with whatever year is being done, and February does too. Just not the chart. I may have to explicitly call the sheet name, not merely the range name I declared. I will try that, and get back to you. Your stuff is nice, but I don't think I actually need a dynamic table to accommodate one single additional row every fourth year. :-) The table is fine. The chart needs to be able to be dynamically defined by accepting named ranges as data range input. That I can switch in once every fourth year. I mean I know I can make a separate chart and refer to that chart on leap year and the other during the interim, but is that the most elegant solution? Can I merely make another chart below the first February chart, and make it a 29 day reference, and I guessI don't have to worry about the year, since one 365th tick not filled would be barely noticeable. Seems like a kludge for me to simply add a leap year chart on my February Plot sheet, such that any monkey can tell which set of dual data is valid for the year they are in. Hmmm... divisible by 4, by 100, by 400... What's all this leap year crud?! |
#5
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On Fri, 22 May 2009 15:52:14 -0400, "Jon Peltier"
wrote: If you can use a defined name for the X and Y values for each series in the chart, you can build dynamic charts that don't use VBA. Somehow when I read your description, I assumed you didn't want to do that. The defined names only work for the individual X and Y values, not for the total source data range. For info on creating dynamic charts: http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/WordPress/dynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "FatBytestard" wrote in message .. . On Thu, 21 May 2009 23:40:15 -0400, "Jon Peltier" wrote: This post has some VBA techniques near the end: http://peltiertech.com/WordPress/dyn...t-source-data/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ So you are saying that this is the best way to handle say leap year? I was wanting to get along without any VB code. Oh well. The data table for the 29th day would only receive data if there were a day 29, a leap year. So I leave the table at 29 rows, and only select 28 rows for the chart. On leap year, I want to select 29 rows as the data set referred to by the chart. All my other months' days keep track with whatever year is being done, and February does too. Just not the chart. I may have to explicitly call the sheet name, not merely the range name I declared. I will try that, and get back to you. Your stuff is nice, but I don't think I actually need a dynamic table to accommodate one single additional row every fourth year. :-) The table is fine. The chart needs to be able to be dynamically defined by accepting named ranges as data range input. That I can switch in once every fourth year. I mean I know I can make a separate chart and refer to that chart on leap year and the other during the interim, but is that the most elegant solution? Can I merely make another chart below the first February chart, and make it a 29 day reference, and I guessI don't have to worry about the year, since one 365th tick not filled would be barely noticeable. Seems like a kludge for me to simply add a leap year chart on my February Plot sheet, such that any monkey can tell which set of dual data is valid for the year they are in. Hmmm... divisible by 4, by 100, by 400... What's all this leap year crud?! Perhaps the best way to do this is to define two February tables and do a simple VB leap year test and set a value based on the test results that hides one or the other table, and that chart always looks at only the active table. Sound good? Or two tables AND two charts, and the VB script merely hides the two that are not pertinent in the year selected. |
#6
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
I guess I don't understand what you're trying to do, and how you're going
about it. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "FatBytestard" wrote in message ... On Fri, 22 May 2009 15:52:14 -0400, "Jon Peltier" wrote: If you can use a defined name for the X and Y values for each series in the chart, you can build dynamic charts that don't use VBA. Somehow when I read your description, I assumed you didn't want to do that. The defined names only work for the individual X and Y values, not for the total source data range. For info on creating dynamic charts: http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/WordPress/dynamic-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "FatBytestard" wrote in message . .. On Thu, 21 May 2009 23:40:15 -0400, "Jon Peltier" wrote: This post has some VBA techniques near the end: http://peltiertech.com/WordPress/dyn...t-source-data/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ So you are saying that this is the best way to handle say leap year? I was wanting to get along without any VB code. Oh well. The data table for the 29th day would only receive data if there were a day 29, a leap year. So I leave the table at 29 rows, and only select 28 rows for the chart. On leap year, I want to select 29 rows as the data set referred to by the chart. All my other months' days keep track with whatever year is being done, and February does too. Just not the chart. I may have to explicitly call the sheet name, not merely the range name I declared. I will try that, and get back to you. Your stuff is nice, but I don't think I actually need a dynamic table to accommodate one single additional row every fourth year. :-) The table is fine. The chart needs to be able to be dynamically defined by accepting named ranges as data range input. That I can switch in once every fourth year. I mean I know I can make a separate chart and refer to that chart on leap year and the other during the interim, but is that the most elegant solution? Can I merely make another chart below the first February chart, and make it a 29 day reference, and I guessI don't have to worry about the year, since one 365th tick not filled would be barely noticeable. Seems like a kludge for me to simply add a leap year chart on my February Plot sheet, such that any monkey can tell which set of dual data is valid for the year they are in. Hmmm... divisible by 4, by 100, by 400... What's all this leap year crud?! Perhaps the best way to do this is to define two February tables and do a simple VB leap year test and set a value based on the test results that hides one or the other table, and that chart always looks at only the active table. Sound good? Or two tables AND two charts, and the VB script merely hides the two that are not pertinent in the year selected. |
#7
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On Sun, 24 May 2009 08:43:26 -0400, "Jon Peltier"
wrote: I guess I don't understand what you're trying to do, and how you're going about it. I developed a workbook which contains 12 month 'tables' (worksheets) that allow two patient test data entries per day. There are 12 monthly charts that track the test results. I also have one 365 day chart. My February month is 29 rows on a leap year, but only 28 otherwise. The chart will have an error as will the year unless I handle leap years (or non-leap years) such that the monthly chart and yearly chart track correctly, according to the row count on the February sheet. I was thinking of simply making a duplicate "table" a few rows below the current table, and remove the 29th line from one, and have the use apply test data to the right one, or run a macro to hide one table. It still need two charts (three counting the additional annum chart) for February to keep the error out, so I was wanting to dynamically declare the chart spec. It appears I may be able to one axis at a time, just not the data block itself, which is what I was trying to point at. So, I thought I wanted a test for leap year, and would likely have to develop a little engine to give the user the access to the right one only, and the right chart(s) would get declared, or referred to. So, my other solution was simply to make two tables on the same sheet, and two charts on the same chart sheet (February), and make a little test to hide the appropriate ones. That was as opposed to dynamically declaring things after testing for the leap year "flag setting". Anyway, my first page has the Year, name (patient), date, and set points for the tables to show red values at, and the doctor phone number. That data gets referred to on each sheet and the month's date column fills in the day of the week based on the year selected. Those selections do cause date entries to transit from 2/28/xxxx to 3/1/xxxx correctly on non-leap years, and also on leap years after passing 2/29/xxxx, so I could test for that somewhere. Otherwise it screws up my Feruary sheet. :-) Not sure if I related this very well. |
#8
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
Make your life simple. Suppose your dates are in column A, from A2 (Feb 1)
to A30 (Feb 29). Put this formula into cell A30: =IF(MONTH(A29+1)=2,A29+1,NA()) This puts Feb 29 into the cell on a leap year, or #N/A otherwise. The #N/A will not be plotted in an XY chart or a line chart, and the data will progress in one step from 2/28 to 3/1 on non-leap years. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "FatBytestard" wrote in message ... On Sun, 24 May 2009 08:43:26 -0400, "Jon Peltier" wrote: I guess I don't understand what you're trying to do, and how you're going about it. I developed a workbook which contains 12 month 'tables' (worksheets) that allow two patient test data entries per day. There are 12 monthly charts that track the test results. I also have one 365 day chart. My February month is 29 rows on a leap year, but only 28 otherwise. The chart will have an error as will the year unless I handle leap years (or non-leap years) such that the monthly chart and yearly chart track correctly, according to the row count on the February sheet. I was thinking of simply making a duplicate "table" a few rows below the current table, and remove the 29th line from one, and have the use apply test data to the right one, or run a macro to hide one table. It still need two charts (three counting the additional annum chart) for February to keep the error out, so I was wanting to dynamically declare the chart spec. It appears I may be able to one axis at a time, just not the data block itself, which is what I was trying to point at. So, I thought I wanted a test for leap year, and would likely have to develop a little engine to give the user the access to the right one only, and the right chart(s) would get declared, or referred to. So, my other solution was simply to make two tables on the same sheet, and two charts on the same chart sheet (February), and make a little test to hide the appropriate ones. That was as opposed to dynamically declaring things after testing for the leap year "flag setting". Anyway, my first page has the Year, name (patient), date, and set points for the tables to show red values at, and the doctor phone number. That data gets referred to on each sheet and the month's date column fills in the day of the week based on the year selected. Those selections do cause date entries to transit from 2/28/xxxx to 3/1/xxxx correctly on non-leap years, and also on leap years after passing 2/29/xxxx, so I could test for that somewhere. Otherwise it screws up my Feruary sheet. :-) Not sure if I related this very well. |
#9
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On Sat, 30 May 2009 09:23:23 -0400, "Jon Peltier"
wrote: Make your life simple. Suppose your dates are in column A, from A2 (Feb 1) to A30 (Feb 29). Put this formula into cell A30: =IF(MONTH(A29+1)=2,A29+1,NA()) This puts Feb 29 into the cell on a leap year, or #N/A otherwise. The #N/A will not be plotted in an XY chart or a line chart, and the data will progress in one step from 2/28 to 3/1 on non-leap years. - Jon That 'IF(MONTH' test segment is what I was looking for. My third month is on a separate sheet and will always read as the first day of the third month on the first row. I am doing this for Feb only and this will allow me to selectively fill both the date column and the day of week column, which also shows up in the chart. So this will make the 29 row blank (#N/A) on other than leap year, and that blank row will not get used on the chart sheet, even though it is based on the 29 row range? That is what I gathered from the letter part of you response, and this will even keep that row out of the 365 day years.? I think this may be exactly what I was after. This switch, in fact is short and sweet, whereas I was trying to switch entire charts or worksheets on which that given month would be based on. I will let you know. Thank you. |
#10
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
All true. I tested it before posting to reassure myself that my memory was
correct and to help with the description. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Only two more weeks! Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message ... On Sat, 30 May 2009 09:23:23 -0400, "Jon Peltier" wrote: Make your life simple. Suppose your dates are in column A, from A2 (Feb 1) to A30 (Feb 29). Put this formula into cell A30: =IF(MONTH(A29+1)=2,A29+1,NA()) This puts Feb 29 into the cell on a leap year, or #N/A otherwise. The #N/A will not be plotted in an XY chart or a line chart, and the data will progress in one step from 2/28 to 3/1 on non-leap years. - Jon That 'IF(MONTH' test segment is what I was looking for. My third month is on a separate sheet and will always read as the first day of the third month on the first row. I am doing this for Feb only and this will allow me to selectively fill both the date column and the day of week column, which also shows up in the chart. So this will make the 29 row blank (#N/A) on other than leap year, and that blank row will not get used on the chart sheet, even though it is based on the 29 row range? That is what I gathered from the letter part of you response, and this will even keep that row out of the 365 day years.? I think this may be exactly what I was after. This switch, in fact is short and sweet, whereas I was trying to switch entire charts or worksheets on which that given month would be based on. I will let you know. Thank you. |
#11
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]() Well, it does place the NA in the date cell just fine, and the chart does not put those tags in, but it does still make a 29 day chart, and despite telling it to ignore the data, if there is data in the data cells, it appears in the non-labeled 29th day in the chart. What I would need to do is actually delete the whole line in the data set, because the chart is pivot based, and the table adds a line and incorporates it into the chart automatically when data is added to the row just under the last row.fine. It also concatenates the chart size when said row is removed. I just found out that this all works fine by merely hiding the row, so my test needs to trigger a hide or unhide event at a row location. The chart follows this move whether there is data in the hidden row or not. So it is pretty close. I think I could do this now with a macro Since I know how to do the test, and I know how to hide and unhide via menu, and I recall seeing code for it (hiding) while in the help file too. Is there a straight excel function that would hide or unhide a row, or will VB be the only way? Thanks for the test string. That ties it all together. I know it is simple, but I do not know all the command functions, so I could not consider even attempting the test string. When I attempted the hide while I authored this post, I found that the chart follows the hide event. Thanks again. Pretty close to a dynamic February data table and tracking charts. Only a few decisions left to make. My almost ready chart has been DL'd on MS' site 100 times. :-) After this fix, it will be as good as it gets... or as good as it needs to be anyway. On Wed, 3 Jun 2009 08:16:48 -0400, "Jon Peltier" wrote: All true. I tested it before posting to reassure myself that my memory was correct and to help with the description. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Only two more weeks! Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message .. . On Sat, 30 May 2009 09:23:23 -0400, "Jon Peltier" wrote: Make your life simple. Suppose your dates are in column A, from A2 (Feb 1) to A30 (Feb 29). Put this formula into cell A30: =IF(MONTH(A29+1)=2,A29+1,NA()) This puts Feb 29 into the cell on a leap year, or #N/A otherwise. The #N/A will not be plotted in an XY chart or a line chart, and the data will progress in one step from 2/28 to 3/1 on non-leap years. - Jon That 'IF(MONTH' test segment is what I was looking for. My third month is on a separate sheet and will always read as the first day of the third month on the first row. I am doing this for Feb only and this will allow me to selectively fill both the date column and the day of week column, which also shows up in the chart. So this will make the 29 row blank (#N/A) on other than leap year, and that blank row will not get used on the chart sheet, even though it is based on the 29 row range? That is what I gathered from the letter part of you response, and this will even keep that row out of the 365 day years.? I think this may be exactly what I was after. This switch, in fact is short and sweet, whereas I was trying to switch entire charts or worksheets on which that given month would be based on. I will let you know. Thank you. |
#12
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
I tested before I posted before, and I've just tested again. If the last
cell has #N/A in it, the chart doesn't even include a blank slot for the date. This is tested on a line chart and a column chart, both having date-scale axes. In a line chart, there will be no space even with a category (text label) axis if you also have a condition on the X value that returns NA() when the date is #N/A. A column chart with a category (text label) axis will have a slot with #N/A as the label. - Jon ------- Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Archimedes' Lever" wrote in message ... Well, it does place the NA in the date cell just fine, and the chart does not put those tags in, but it does still make a 29 day chart, and despite telling it to ignore the data, if there is data in the data cells, it appears in the non-labeled 29th day in the chart. What I would need to do is actually delete the whole line in the data set, because the chart is pivot based, and the table adds a line and incorporates it into the chart automatically when data is added to the row just under the last row.fine. It also concatenates the chart size when said row is removed. I just found out that this all works fine by merely hiding the row, so my test needs to trigger a hide or unhide event at a row location. The chart follows this move whether there is data in the hidden row or not. So it is pretty close. I think I could do this now with a macro Since I know how to do the test, and I know how to hide and unhide via menu, and I recall seeing code for it (hiding) while in the help file too. Is there a straight excel function that would hide or unhide a row, or will VB be the only way? Thanks for the test string. That ties it all together. I know it is simple, but I do not know all the command functions, so I could not consider even attempting the test string. When I attempted the hide while I authored this post, I found that the chart follows the hide event. Thanks again. Pretty close to a dynamic February data table and tracking charts. Only a few decisions left to make. My almost ready chart has been DL'd on MS' site 100 times. :-) After this fix, it will be as good as it gets... or as good as it needs to be anyway. On Wed, 3 Jun 2009 08:16:48 -0400, "Jon Peltier" wrote: All true. I tested it before posting to reassure myself that my memory was correct and to help with the description. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Only two more weeks! Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message . .. On Sat, 30 May 2009 09:23:23 -0400, "Jon Peltier" wrote: Make your life simple. Suppose your dates are in column A, from A2 (Feb 1) to A30 (Feb 29). Put this formula into cell A30: =IF(MONTH(A29+1)=2,A29+1,NA()) This puts Feb 29 into the cell on a leap year, or #N/A otherwise. The #N/A will not be plotted in an XY chart or a line chart, and the data will progress in one step from 2/28 to 3/1 on non-leap years. - Jon That 'IF(MONTH' test segment is what I was looking for. My third month is on a separate sheet and will always read as the first day of the third month on the first row. I am doing this for Feb only and this will allow me to selectively fill both the date column and the day of week column, which also shows up in the chart. So this will make the 29 row blank (#N/A) on other than leap year, and that blank row will not get used on the chart sheet, even though it is based on the 29 row range? That is what I gathered from the letter part of you response, and this will even keep that row out of the 365 day years.? I think this may be exactly what I was after. This switch, in fact is short and sweet, whereas I was trying to switch entire charts or worksheets on which that given month would be based on. I will let you know. Thank you. |
#13
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On Fri, 5 Jun 2009 07:08:23 -0400, "Jon Peltier"
wrote: I tested before I posted before, and I've just tested again. If the last cell has #N/A in it, the chart doesn't even include a blank slot for the date. This is tested on a line chart and a column chart, both having date-scale axes. In a line chart, there will be no space even with a category (text label) axis if you also have a condition on the X value that returns NA() when the date is #N/A. A column chart with a category (text label) axis will have a slot with #N/A as the label. - Jon ------- Mine adds the slot for years with a leap year, but when it inserts #N/A as it does correctly in the first two columns of my data range, the chart blanks the slot labels, but the slot remains. Hiding and unhiding the row most definitely works. Perhaps my improper function is related to the chart type, as in the chart is a pivot chart. Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Archimedes' Lever" wrote in message .. . Well, it does place the NA in the date cell just fine, and the chart does not put those tags in, but it does still make a 29 day chart, and despite telling it to ignore the data, if there is data in the data cells, it appears in the non-labeled 29th day in the chart. What I would need to do is actually delete the whole line in the data set, because the chart is pivot based, and the table adds a line and incorporates it into the chart automatically when data is added to the row just under the last row.fine. It also concatenates the chart size when said row is removed. I just found out that this all works fine by merely hiding the row, so my test needs to trigger a hide or unhide event at a row location. The chart follows this move whether there is data in the hidden row or not. So it is pretty close. I think I could do this now with a macro Since I know how to do the test, and I know how to hide and unhide via menu, and I recall seeing code for it (hiding) while in the help file too. Is there a straight excel function that would hide or unhide a row, or will VB be the only way? Thanks for the test string. That ties it all together. I know it is simple, but I do not know all the command functions, so I could not consider even attempting the test string. When I attempted the hide while I authored this post, I found that the chart follows the hide event. Thanks again. Pretty close to a dynamic February data table and tracking charts. Only a few decisions left to make. My almost ready chart has been DL'd on MS' site 100 times. :-) After this fix, it will be as good as it gets... or as good as it needs to be anyway. On Wed, 3 Jun 2009 08:16:48 -0400, "Jon Peltier" wrote: All true. I tested it before posting to reassure myself that my memory was correct and to help with the description. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Only two more weeks! Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message ... On Sat, 30 May 2009 09:23:23 -0400, "Jon Peltier" wrote: Make your life simple. Suppose your dates are in column A, from A2 (Feb 1) to A30 (Feb 29). Put this formula into cell A30: =IF(MONTH(A29+1)=2,A29+1,NA()) This puts Feb 29 into the cell on a leap year, or #N/A otherwise. The #N/A will not be plotted in an XY chart or a line chart, and the data will progress in one step from 2/28 to 3/1 on non-leap years. - Jon That 'IF(MONTH' test segment is what I was looking for. My third month is on a separate sheet and will always read as the first day of the third month on the first row. I am doing this for Feb only and this will allow me to selectively fill both the date column and the day of week column, which also shows up in the chart. So this will make the 29 row blank (#N/A) on other than leap year, and that blank row will not get used on the chart sheet, even though it is based on the 29 row range? That is what I gathered from the letter part of you response, and this will even keep that row out of the 365 day years.? I think this may be exactly what I was after. This switch, in fact is short and sweet, whereas I was trying to switch entire charts or worksheets on which that given month would be based on. I will let you know. Thank you. |
#14
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
A pivot chart, eh? They're not so flexible. Keep in mind that it's always
best to share as many details as possible when introducing the post, like chart type, version number, specific formula that's giving difficulties. So how are you making a pivot table that gives you a slot for 29 Feb 2009? Pivot tables don't make up dates. Use the whole dates in the pivot table, but if you only want to show the day number, use a custom date format that only shows the day, not month and year. - Jon ------- Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Archimedes' Lever" wrote in message ... On Fri, 5 Jun 2009 07:08:23 -0400, "Jon Peltier" wrote: I tested before I posted before, and I've just tested again. If the last cell has #N/A in it, the chart doesn't even include a blank slot for the date. This is tested on a line chart and a column chart, both having date-scale axes. In a line chart, there will be no space even with a category (text label) axis if you also have a condition on the X value that returns NA() when the date is #N/A. A column chart with a category (text label) axis will have a slot with #N/A as the label. - Jon ------- Mine adds the slot for years with a leap year, but when it inserts #N/A as it does correctly in the first two columns of my data range, the chart blanks the slot labels, but the slot remains. Hiding and unhiding the row most definitely works. Perhaps my improper function is related to the chart type, as in the chart is a pivot chart. Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Archimedes' Lever" wrote in message . .. Well, it does place the NA in the date cell just fine, and the chart does not put those tags in, but it does still make a 29 day chart, and despite telling it to ignore the data, if there is data in the data cells, it appears in the non-labeled 29th day in the chart. What I would need to do is actually delete the whole line in the data set, because the chart is pivot based, and the table adds a line and incorporates it into the chart automatically when data is added to the row just under the last row.fine. It also concatenates the chart size when said row is removed. I just found out that this all works fine by merely hiding the row, so my test needs to trigger a hide or unhide event at a row location. The chart follows this move whether there is data in the hidden row or not. So it is pretty close. I think I could do this now with a macro Since I know how to do the test, and I know how to hide and unhide via menu, and I recall seeing code for it (hiding) while in the help file too. Is there a straight excel function that would hide or unhide a row, or will VB be the only way? Thanks for the test string. That ties it all together. I know it is simple, but I do not know all the command functions, so I could not consider even attempting the test string. When I attempted the hide while I authored this post, I found that the chart follows the hide event. Thanks again. Pretty close to a dynamic February data table and tracking charts. Only a few decisions left to make. My almost ready chart has been DL'd on MS' site 100 times. :-) After this fix, it will be as good as it gets... or as good as it needs to be anyway. On Wed, 3 Jun 2009 08:16:48 -0400, "Jon Peltier" wrote: All true. I tested it before posting to reassure myself that my memory was correct and to help with the description. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Only two more weeks! Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message m... On Sat, 30 May 2009 09:23:23 -0400, "Jon Peltier" wrote: Make your life simple. Suppose your dates are in column A, from A2 (Feb 1) to A30 (Feb 29). Put this formula into cell A30: =IF(MONTH(A29+1)=2,A29+1,NA()) This puts Feb 29 into the cell on a leap year, or #N/A otherwise. The #N/A will not be plotted in an XY chart or a line chart, and the data will progress in one step from 2/28 to 3/1 on non-leap years. - Jon That 'IF(MONTH' test segment is what I was looking for. My third month is on a separate sheet and will always read as the first day of the third month on the first row. I am doing this for Feb only and this will allow me to selectively fill both the date column and the day of week column, which also shows up in the chart. So this will make the 29 row blank (#N/A) on other than leap year, and that blank row will not get used on the chart sheet, even though it is based on the 29 row range? That is what I gathered from the letter part of you response, and this will even keep that row out of the 365 day years.? I think this may be exactly what I was after. This switch, in fact is short and sweet, whereas I was trying to switch entire charts or worksheets on which that given month would be based on. I will let you know. Thank you. |
#15
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On Fri, 5 Jun 2009 11:29:02 -0400, "Jon Peltier"
wrote: A pivot chart, eh? They're not so flexible. Keep in mind that it's always best to share as many details as possible when introducing the post, like chart type, version number, specific formula that's giving difficulties. So how are you making a pivot table that gives you a slot for 29 Feb 2009? Pivot tables don't make up dates. Use the whole dates in the pivot table, but if you only want to show the day number, use a custom date format that only shows the day, not month and year. It has TWO columns at the start that are BOTH date fields, and are both formatted to show up as day of month, and day of week, so the chart shows all 31 days in January, and the days of the week associated with those days. Since it is a blood pressure tracking workbook, info like that is good to see when the user/patient/doctor wants to examine the data for a trend or spike, etc. I am trying to perfect it. It is on the Microsoft Templates page under community submitted templates. Folks are downloading it, so I want to get it fixed up. I have since made some changes so it is not formatted as it is on the MS site currently. That sheet uses a simple number for the day of the month, so that column was not a date column. Since I grabbed the sheet from someone else, I was unaware that it was a pivot chart, as I have only recently become aware of the animal's existence (thanks). Your help has taught me a lot from what you have given to what I picked up as I have been doing this exercise in error abatement. :-) http://office.microsoft.com/en-us/my...056691033.aspx http://office.microsoft.com/search/r... T101436151033 |
#16
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
If this were my project (sorry, I don't have time to make it mine) I think I
would have used an Excel List or Table as the data entry region, kept dates in one column, and not fed a pivot table with this. I'd build a set of worksheet controls that would allow the user to select a time range to plot, on a chart with a date scale on the X axis. I showed an example that worked like this he Dynamic Charting By Dates - http://pubs.logicalexpressions.com/P...cle.asp?ID=246 ------- Just over a week! Don't miss it! Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Archimedes' Lever" wrote in message ... On Fri, 5 Jun 2009 11:29:02 -0400, "Jon Peltier" wrote: A pivot chart, eh? They're not so flexible. Keep in mind that it's always best to share as many details as possible when introducing the post, like chart type, version number, specific formula that's giving difficulties. So how are you making a pivot table that gives you a slot for 29 Feb 2009? Pivot tables don't make up dates. Use the whole dates in the pivot table, but if you only want to show the day number, use a custom date format that only shows the day, not month and year. It has TWO columns at the start that are BOTH date fields, and are both formatted to show up as day of month, and day of week, so the chart shows all 31 days in January, and the days of the week associated with those days. Since it is a blood pressure tracking workbook, info like that is good to see when the user/patient/doctor wants to examine the data for a trend or spike, etc. I am trying to perfect it. It is on the Microsoft Templates page under community submitted templates. Folks are downloading it, so I want to get it fixed up. I have since made some changes so it is not formatted as it is on the MS site currently. That sheet uses a simple number for the day of the month, so that column was not a date column. Since I grabbed the sheet from someone else, I was unaware that it was a pivot chart, as I have only recently become aware of the animal's existence (thanks). Your help has taught me a lot from what you have given to what I picked up as I have been doing this exercise in error abatement. :-) http://office.microsoft.com/en-us/my...056691033.aspx http://office.microsoft.com/search/r... T101436151033 |
#17
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On Sat, 6 Jun 2009 15:43:55 -0400, "Jon Peltier"
wrote: If this were my project (sorry, I don't have time to make it mine) I think I would have used an Excel List or Table as the data entry region, kept dates in one column, and not fed a pivot table with this. I'd build a set of worksheet controls that would allow the user to select a time range to plot, on a chart with a date scale on the X axis. I showed an example that worked like this he Dynamic Charting By Dates - http://pubs.logicalexpressions.com/P...cle.asp?ID=246 I made this: http://office.microsoft.com/en-us/my...058731033.aspx There is no Feb 29 handling currently as MS does not accept macro enabled template submissions. There are no pivot charts or tables. Feb 29 messes up the charts, even with the #N/A () in place in the date fields, which are one axis. So I omit your IF statement test string, and was using a button to hide or unhide that row in two locations. That actually works, and also likely with the area chart under discussion elsewhere. That is a good way to code it, if it works with all the charts. Is MS going to patch SP2 to solve its problems I have been reading about? Anyway, take a gander at my workbook, if you get a moment. I think the macro buttons could be eliminated, and the test and change be automated in a single macro with no need for user interaction (simply run the test and hide or unhide the rows). Thanks again, Jon, for all the help you have given me. My work is close to completion, and much better than the previous version thanks to your help, and the exercises it put me through. 'Grade' my spreadsheet if you would. I like feedback. I should make it so the user can select that chart plot line colors on the first page as well. That would be cool, as then the user could make easy universal color changes without the recursive exercise of activating all the pertinent sheets and setting the colors and saving. Having a choice dialog would make it point and click. Anyone that monitors and tracks their blood pressure should give it a glance as well. |
#18
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
"The template you are trying to access was not found."
- Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message ... On Sat, 6 Jun 2009 15:43:55 -0400, "Jon Peltier" wrote: If this were my project (sorry, I don't have time to make it mine) I think I would have used an Excel List or Table as the data entry region, kept dates in one column, and not fed a pivot table with this. I'd build a set of worksheet controls that would allow the user to select a time range to plot, on a chart with a date scale on the X axis. I showed an example that worked like this he Dynamic Charting By Dates - http://pubs.logicalexpressions.com/P...cle.asp?ID=246 I made this: http://office.microsoft.com/en-us/my...058731033.aspx There is no Feb 29 handling currently as MS does not accept macro enabled template submissions. There are no pivot charts or tables. Feb 29 messes up the charts, even with the #N/A () in place in the date fields, which are one axis. So I omit your IF statement test string, and was using a button to hide or unhide that row in two locations. That actually works, and also likely with the area chart under discussion elsewhere. That is a good way to code it, if it works with all the charts. Is MS going to patch SP2 to solve its problems I have been reading about? Anyway, take a gander at my workbook, if you get a moment. I think the macro buttons could be eliminated, and the test and change be automated in a single macro with no need for user interaction (simply run the test and hide or unhide the rows). Thanks again, Jon, for all the help you have given me. My work is close to completion, and much better than the previous version thanks to your help, and the exercises it put me through. 'Grade' my spreadsheet if you would. I like feedback. I should make it so the user can select that chart plot line colors on the first page as well. That would be cool, as then the user could make easy universal color changes without the recursive exercise of activating all the pertinent sheets and setting the colors and saving. Having a choice dialog would make it point and click. Anyone that monitors and tracks their blood pressure should give it a glance as well. |
#19
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]() Hunt up on the recent submissions "WallyWallWhackr" or "Blood Pressure Tracker for entire year". It is on the first page of the "recently submitted" community templates. on the templates main page at microsoft.com http://office.microsoft.com/search/r... 100632981033 or, the main templates page and select "recently submitted" in the left column: http://office.microsoft.com/en-us/te...595491033.aspx On Thu, 11 Jun 2009 09:38:26 -0400, "Jon Peltier" wrote: "The template you are trying to access was not found." - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message .. . On Sat, 6 Jun 2009 15:43:55 -0400, "Jon Peltier" wrote: If this were my project (sorry, I don't have time to make it mine) I think I would have used an Excel List or Table as the data entry region, kept dates in one column, and not fed a pivot table with this. I'd build a set of worksheet controls that would allow the user to select a time range to plot, on a chart with a date scale on the X axis. I showed an example that worked like this he Dynamic Charting By Dates - http://pubs.logicalexpressions.com/P...cle.asp?ID=246 I made this: http://office.microsoft.com/en-us/my...058731033.aspx There is no Feb 29 handling currently as MS does not accept macro enabled template submissions. There are no pivot charts or tables. Feb 29 messes up the charts, even with the #N/A () in place in the date fields, which are one axis. So I omit your IF statement test string, and was using a button to hide or unhide that row in two locations. That actually works, and also likely with the area chart under discussion elsewhere. That is a good way to code it, if it works with all the charts. Is MS going to patch SP2 to solve its problems I have been reading about? Anyway, take a gander at my workbook, if you get a moment. I think the macro buttons could be eliminated, and the test and change be automated in a single macro with no need for user interaction (simply run the test and hide or unhide the rows). Thanks again, Jon, for all the help you have given me. My work is close to completion, and much better than the previous version thanks to your help, and the exercises it put me through. 'Grade' my spreadsheet if you would. I like feedback. I should make it so the user can select that chart plot line colors on the first page as well. That would be cool, as then the user could make easy universal color changes without the recursive exercise of activating all the pertinent sheets and setting the colors and saving. Having a choice dialog would make it point and click. Anyone that monitors and tracks their blood pressure should give it a glance as well. |
#20
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
"The template you are trying to access was not found."
I think the problem is that the MS site "helpfully" checks whether I have 2007 installed on my machine (I do, but on a VM, not on the main machine). If not, I get an option to download manually, and when I click on that link, it can't believe I haven't upgraded to the newest, glitteriest spreadsheet program in history, so it can't bring itself to find the file. Why they can't just put up a simple freaking link, I don't know. They've pushed the compatibility pack onto everyone anyway, so why should they care? - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message ... Hunt up on the recent submissions "WallyWallWhackr" or "Blood Pressure Tracker for entire year". It is on the first page of the "recently submitted" community templates. on the templates main page at microsoft.com http://office.microsoft.com/search/r... 100632981033 or, the main templates page and select "recently submitted" in the left column: http://office.microsoft.com/en-us/te...595491033.aspx On Thu, 11 Jun 2009 09:38:26 -0400, "Jon Peltier" wrote: "The template you are trying to access was not found." - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message . .. On Sat, 6 Jun 2009 15:43:55 -0400, "Jon Peltier" wrote: If this were my project (sorry, I don't have time to make it mine) I think I would have used an Excel List or Table as the data entry region, kept dates in one column, and not fed a pivot table with this. I'd build a set of worksheet controls that would allow the user to select a time range to plot, on a chart with a date scale on the X axis. I showed an example that worked like this he Dynamic Charting By Dates - http://pubs.logicalexpressions.com/P...cle.asp?ID=246 I made this: http://office.microsoft.com/en-us/my...058731033.aspx There is no Feb 29 handling currently as MS does not accept macro enabled template submissions. There are no pivot charts or tables. Feb 29 messes up the charts, even with the #N/A () in place in the date fields, which are one axis. So I omit your IF statement test string, and was using a button to hide or unhide that row in two locations. That actually works, and also likely with the area chart under discussion elsewhere. That is a good way to code it, if it works with all the charts. Is MS going to patch SP2 to solve its problems I have been reading about? Anyway, take a gander at my workbook, if you get a moment. I think the macro buttons could be eliminated, and the test and change be automated in a single macro with no need for user interaction (simply run the test and hide or unhide the rows). Thanks again, Jon, for all the help you have given me. My work is close to completion, and much better than the previous version thanks to your help, and the exercises it put me through. 'Grade' my spreadsheet if you would. I like feedback. I should make it so the user can select that chart plot line colors on the first page as well. That would be cool, as then the user could make easy universal color changes without the recursive exercise of activating all the pertinent sheets and setting the colors and saving. Having a choice dialog would make it point and click. Anyone that monitors and tracks their blood pressure should give it a glance as well. |
#21
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
No, the problem wasn't the lack of 2007. I tried from my VM, which has 2007,
and the web site couldn't find 2007. When I went ahead anyway, the template was not found. Then I tried using IE (I was using FireFox) with or without 2007, same result. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jon Peltier" wrote in message ... "The template you are trying to access was not found." I think the problem is that the MS site "helpfully" checks whether I have 2007 installed on my machine (I do, but on a VM, not on the main machine). If not, I get an option to download manually, and when I click on that link, it can't believe I haven't upgraded to the newest, glitteriest spreadsheet program in history, so it can't bring itself to find the file. Why they can't just put up a simple freaking link, I don't know. They've pushed the compatibility pack onto everyone anyway, so why should they care? - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message ... Hunt up on the recent submissions "WallyWallWhackr" or "Blood Pressure Tracker for entire year". It is on the first page of the "recently submitted" community templates. on the templates main page at microsoft.com http://office.microsoft.com/search/r... 100632981033 or, the main templates page and select "recently submitted" in the left column: http://office.microsoft.com/en-us/te...595491033.aspx On Thu, 11 Jun 2009 09:38:26 -0400, "Jon Peltier" wrote: "The template you are trying to access was not found." - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message ... On Sat, 6 Jun 2009 15:43:55 -0400, "Jon Peltier" wrote: If this were my project (sorry, I don't have time to make it mine) I think I would have used an Excel List or Table as the data entry region, kept dates in one column, and not fed a pivot table with this. I'd build a set of worksheet controls that would allow the user to select a time range to plot, on a chart with a date scale on the X axis. I showed an example that worked like this he Dynamic Charting By Dates - http://pubs.logicalexpressions.com/P...cle.asp?ID=246 I made this: http://office.microsoft.com/en-us/my...058731033.aspx There is no Feb 29 handling currently as MS does not accept macro enabled template submissions. There are no pivot charts or tables. Feb 29 messes up the charts, even with the #N/A () in place in the date fields, which are one axis. So I omit your IF statement test string, and was using a button to hide or unhide that row in two locations. That actually works, and also likely with the area chart under discussion elsewhere. That is a good way to code it, if it works with all the charts. Is MS going to patch SP2 to solve its problems I have been reading about? Anyway, take a gander at my workbook, if you get a moment. I think the macro buttons could be eliminated, and the test and change be automated in a single macro with no need for user interaction (simply run the test and hide or unhide the rows). Thanks again, Jon, for all the help you have given me. My work is close to completion, and much better than the previous version thanks to your help, and the exercises it put me through. 'Grade' my spreadsheet if you would. I like feedback. I should make it so the user can select that chart plot line colors on the first page as well. That would be cool, as then the user could make easy universal color changes without the recursive exercise of activating all the pertinent sheets and setting the colors and saving. Having a choice dialog would make it point and click. Anyone that monitors and tracks their blood pressure should give it a glance as well. |
#22
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On Thu, 11 Jun 2009 14:43:51 -0400, "Jon Peltier"
wrote: I think the problem is that the MS site "helpfully" checks whether I have 2007 installed on my machine (I do, but on a VM, not on the main machine). That is correct. When I am at work, my templates do not show up as we are 2k3 at work. |
#23
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]() No? Darn. http://office.microsoft.com/search/r... 101436151033 The string has "assetid" in it. I wonder what that could be...? Can you not get a friend to grab it for you and send it to you? At least they finally got the thumbnail screenshots working. Dang! You cannot see that either! On Thu, 11 Jun 2009 14:43:51 -0400, "Jon Peltier" wrote: "The template you are trying to access was not found." I think the problem is that the MS site "helpfully" checks whether I have 2007 installed on my machine (I do, but on a VM, not on the main machine). If not, I get an option to download manually, and when I click on that link, it can't believe I haven't upgraded to the newest, glitteriest spreadsheet program in history, so it can't bring itself to find the file. Why they can't just put up a simple freaking link, I don't know. They've pushed the compatibility pack onto everyone anyway, so why should they care? - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message .. . Hunt up on the recent submissions "WallyWallWhackr" or "Blood Pressure Tracker for entire year". It is on the first page of the "recently submitted" community templates. on the templates main page at microsoft.com http://office.microsoft.com/search/r... 100632981033 or, the main templates page and select "recently submitted" in the left column: http://office.microsoft.com/en-us/te...595491033.aspx On Thu, 11 Jun 2009 09:38:26 -0400, "Jon Peltier" wrote: "The template you are trying to access was not found." - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message ... On Sat, 6 Jun 2009 15:43:55 -0400, "Jon Peltier" wrote: If this were my project (sorry, I don't have time to make it mine) I think I would have used an Excel List or Table as the data entry region, kept dates in one column, and not fed a pivot table with this. I'd build a set of worksheet controls that would allow the user to select a time range to plot, on a chart with a date scale on the X axis. I showed an example that worked like this he Dynamic Charting By Dates - http://pubs.logicalexpressions.com/P...cle.asp?ID=246 I made this: http://office.microsoft.com/en-us/my...058731033.aspx There is no Feb 29 handling currently as MS does not accept macro enabled template submissions. There are no pivot charts or tables. Feb 29 messes up the charts, even with the #N/A () in place in the date fields, which are one axis. So I omit your IF statement test string, and was using a button to hide or unhide that row in two locations. That actually works, and also likely with the area chart under discussion elsewhere. That is a good way to code it, if it works with all the charts. Is MS going to patch SP2 to solve its problems I have been reading about? Anyway, take a gander at my workbook, if you get a moment. I think the macro buttons could be eliminated, and the test and change be automated in a single macro with no need for user interaction (simply run the test and hide or unhide the rows). Thanks again, Jon, for all the help you have given me. My work is close to completion, and much better than the previous version thanks to your help, and the exercises it put me through. 'Grade' my spreadsheet if you would. I like feedback. I should make it so the user can select that chart plot line colors on the first page as well. That would be cool, as then the user could make easy universal color changes without the recursive exercise of activating all the pertinent sheets and setting the colors and saving. Having a choice dialog would make it point and click. Anyone that monitors and tracks their blood pressure should give it a glance as well. |
#24
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On Thu, 11 Jun 2009 15:25:43 -0400, "Jon Peltier"
wrote: No, the problem wasn't the lack of 2007. I tried from my VM, which has 2007, and the web site couldn't find 2007. When I went ahead anyway, the template was not found. Then I tried using IE (I was using FireFox) with or without 2007, same result. It is there, andI definitely get a different list at work than I do here.Are you sure a virtualized installation gets "seen" correctly at the MS site? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jon Peltier" wrote in message ... "The template you are trying to access was not found." I think the problem is that the MS site "helpfully" checks whether I have 2007 installed on my machine (I do, but on a VM, not on the main machine). If not, I get an option to download manually, and when I click on that link, it can't believe I haven't upgraded to the newest, glitteriest spreadsheet program in history, so it can't bring itself to find the file. Why they can't just put up a simple freaking link, I don't know. They've pushed the compatibility pack onto everyone anyway, so why should they care? - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message ... Hunt up on the recent submissions "WallyWallWhackr" or "Blood Pressure Tracker for entire year". It is on the first page of the "recently submitted" community templates. on the templates main page at microsoft.com http://office.microsoft.com/search/r... 100632981033 or, the main templates page and select "recently submitted" in the left column: http://office.microsoft.com/en-us/te...595491033.aspx On Thu, 11 Jun 2009 09:38:26 -0400, "Jon Peltier" wrote: "The template you are trying to access was not found." - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Archimedes' Lever" wrote in message m... On Sat, 6 Jun 2009 15:43:55 -0400, "Jon Peltier" wrote: If this were my project (sorry, I don't have time to make it mine) I think I would have used an Excel List or Table as the data entry region, kept dates in one column, and not fed a pivot table with this. I'd build a set of worksheet controls that would allow the user to select a time range to plot, on a chart with a date scale on the X axis. I showed an example that worked like this he Dynamic Charting By Dates - http://pubs.logicalexpressions.com/P...cle.asp?ID=246 I made this: http://office.microsoft.com/en-us/my...058731033.aspx There is no Feb 29 handling currently as MS does not accept macro enabled template submissions. There are no pivot charts or tables. Feb 29 messes up the charts, even with the #N/A () in place in the date fields, which are one axis. So I omit your IF statement test string, and was using a button to hide or unhide that row in two locations. That actually works, and also likely with the area chart under discussion elsewhere. That is a good way to code it, if it works with all the charts. Is MS going to patch SP2 to solve its problems I have been reading about? Anyway, take a gander at my workbook, if you get a moment. I think the macro buttons could be eliminated, and the test and change be automated in a single macro with no need for user interaction (simply run the test and hide or unhide the rows). Thanks again, Jon, for all the help you have given me. My work is close to completion, and much better than the previous version thanks to your help, and the exercises it put me through. 'Grade' my spreadsheet if you would. I like feedback. I should make it so the user can select that chart plot line colors on the first page as well. That would be cool, as then the user could make easy universal color changes without the recursive exercise of activating all the pertinent sheets and setting the colors and saving. Having a choice dialog would make it point and click. Anyone that monitors and tracks their blood pressure should give it a glance as well. |
#25
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]() "Archimedes' Lever" wrote in message ... On Thu, 11 Jun 2009 15:25:43 -0400, "Jon Peltier" wrote: No, the problem wasn't the lack of 2007. I tried from my VM, which has 2007, and the web site couldn't find 2007. When I went ahead anyway, the template was not found. Then I tried using IE (I was using FireFox) with or without 2007, same result. It is there, andI definitely get a different list at work than I do here.Are you sure a virtualized installation gets "seen" correctly at the MS site? Neither my VMs nor my regular machine worked. I don't know why they can't just post a link, like the rest of the world does. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ |
#26
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
On Mon, 15 Jun 2009 22:38:33 -0400, "Jon Peltier"
wrote: "Archimedes' Lever" wrote in message .. . On Thu, 11 Jun 2009 15:25:43 -0400, "Jon Peltier" wrote: No, the problem wasn't the lack of 2007. I tried from my VM, which has 2007, and the web site couldn't find 2007. When I went ahead anyway, the template was not found. Then I tried using IE (I was using FireFox) with or without 2007, same result. It is there, andI definitely get a different list at work than I do here.Are you sure a virtualized installation gets "seen" correctly at the MS site? Neither my VMs nor my regular machine worked. I don't know why they can't just post a link, like the rest of the world does. - Jon They segregate o2k7 and keep you from DLing "non-compliant" templates for o2k3. I can post it to a hosting site. http://www.mediafire.com/?zmzqznvomxa |
#27
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]() "Archimedes' Lever" wrote in message ... On Mon, 15 Jun 2009 22:38:33 -0400, "Jon Peltier" wrote: "Archimedes' Lever" wrote in message . .. On Thu, 11 Jun 2009 15:25:43 -0400, "Jon Peltier" wrote: No, the problem wasn't the lack of 2007. I tried from my VM, which has 2007, and the web site couldn't find 2007. When I went ahead anyway, the template was not found. Then I tried using IE (I was using FireFox) with or without 2007, same result. It is there, andI definitely get a different list at work than I do here.Are you sure a virtualized installation gets "seen" correctly at the MS site? Neither my VMs nor my regular machine worked. I don't know why they can't just post a link, like the rest of the world does. - Jon They segregate o2k7 and keep you from DLing "non-compliant" templates for o2k3. So they should let me click on a link for "Download Excel 2007 Workbook", and if it doesn't work in 2003 it's my problem. Why do they feel the need to babysit all of their users? I can post it to a hosting site. http://www.mediafire.com/?zmzqznvomxa Thanks. The problem is that you're using a multiple-level category axis. #N/A is treated as a bit of text, and the axis extends to include the #N/A in the date column. If you're only protecting the charts to prevent the user accidentally breaking something, don't bother with a password. I was not able to change the category axis data range to verify that a single level category axis scale, using #N/A for the 29th, would not show Feb 29 on non leap years. Well, actually I could break the password. Most passwords in Office are not very secure. And yes, if the X values includes only one column, not two, then the axis behaves as I told you it would. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Cond. Format Data Bars of range based on values of another range | Excel Worksheet Functions | |||
Spreadsheet summary automatically referrences totals of worksheets | Excel Worksheet Functions | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
How do I link data from a horizontal range to a vertical range? | Excel Worksheet Functions | |||
Couting the number of referrences that... | Excel Worksheet Functions |