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