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
|
|||
|
|||
Can charts automatically change by entering new range of dates
Hi Jon,
Actually the first part of your email would be exactly what I want to do. Be able to instead of having the popup box to ask where the data is coming from instead to just have either a popup asking what Day(s) you want to chart or even a drop down menu with dates listed for a starting date and say another for end date or just one doesn't matter. I was able to get the macro going and change for my chart default look so that was good but just unable to get it to chart by user entered date instead of the usual just point and click on the data. As well as it is a setup to make one chart but I do need to make 4 charts and preferably just at once. So basically just say enter in May 10-May 18 and have the chart automatically know where the data for those dates is and make 4 separate charts all together. If thats not possible I could always just do it 4 separate times just trying to make it easier on people at work to be able to go in there themselves and do it as easy as possible. If you want I can send you a copy of the spreadsheet of data for example just change my #s and see if you can see what you can do to get this going. Thanks again for your help Sean "Jon Peltier" wrote: Here's that development project I alluded to. You need to change the interface, replacing the simple inputbox with a userform to list the available dates. Then the code has to somehow examine the data and parse out just what you need. Or... You could set up a filter on your data, and use it to display the date you want, and hide the rest. By default, data in hidden cells do not appear in the chart. That's if the dates are in a single column, with the data lined up in sequential rows. If you have different columns for each date's data, use something like this: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Sounds great ya I was trying out a few things at work and I can easily change the chart type by changing the default and the basic outlook of the chart just in the VBE, going to be nice to learn all of it. Only thing is the options I like with the popup asking where the chart to be put but when it asks for where the data is from I want to be able to type say May 12 and have 4 charts come up with the data I have in my system from May 12. Like for example one chart would be my cash/charge sales, one would be my accounts opened/closed, one would be $/contract compared to last year and finally my Month to Date sales compared to last years. Those are the 4 charts I want to create based on just entering in a date. I am thinking once I get it going I'll probably have to in the code write alot of IF='s right? like basically if=May 12 then it knows where to find the data and then create 4 charts out of it? Thanks "Jon Peltier" wrote: The procedure as posted is pretty simple. It can certainly be made as complicated as you'd like, changing the chart type (I think it uses the default, so you could change your default to make different types), even making multiple charts from a single selected range. Without knowing details, it's not easy to make specific recommendations, and of course, this starts to become a development project once it starts to grow custom features. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hey Jon, Wow thanks for those links. The one I found that will work the best is the Interactive Chart Creation as anyone can pick where they want the chart to go and what data boom done. Few questions though. It does come up as a built-in chart style which I would prefer either a column or pie chart instead is there a way in the macro/vbe to change so instead of the built-in one thats in there to make a different kind of chart instead so I don't have to change the chart type after its created. As well for my weekly report I do end up having around 4 different charts plotting 2 stats on each. Is there a way with this interactive setup to generate 4 different charts at one time like that instead of doing one and then just following the same set up and doing it again to 3 more? Thanks again "Jon Peltier" wrote: I don't think a macro would be any easier than setting up a worksheet- or formula-based solution, especially if you're not familiar with VBA. There are no "easy macro setups" if you don't know how to set up the problem. VBA isn't magic, it only does what Excel can do, but allows you to do it automatically. I suggest you refer to the tutorial I cited, perhaps after reading about dynamic charts and defined names. Dynamic Charts: http://peltiertech.com/Excel/Charts/Dynamics.html http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/Excel/Charts/DynamicLast12.html http://peltiertech.com/Excel/Charts/...hartLinks.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... And BTW would it be easier to be creating a macro for this setup or no?.. If so whats the best place I can go to to find out an easy macro setup? "Sean Moor" wrote: I am creating a Dashboard report for my Accounting department at work and I have a weekly one I set up with links for the series of a chart to another worksheet. Easy. But what I am trying to do is instead of creating a new one each week by changing the link location for the data is there a way I can just enter in a range of dates and the charts automatically change and filer in the new data without me having to change the link in the series where the data entry is coming from?? |
#12
|
|||
|
|||
Can charts automatically change by entering new range of dates
Have you seen this article?
http://pubs.logicalexpressions.com/P...cle.asp?ID=246 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hi Jon, Actually the first part of your email would be exactly what I want to do. Be able to instead of having the popup box to ask where the data is coming from instead to just have either a popup asking what Day(s) you want to chart or even a drop down menu with dates listed for a starting date and say another for end date or just one doesn't matter. I was able to get the macro going and change for my chart default look so that was good but just unable to get it to chart by user entered date instead of the usual just point and click on the data. As well as it is a setup to make one chart but I do need to make 4 charts and preferably just at once. So basically just say enter in May 10-May 18 and have the chart automatically know where the data for those dates is and make 4 separate charts all together. If thats not possible I could always just do it 4 separate times just trying to make it easier on people at work to be able to go in there themselves and do it as easy as possible. If you want I can send you a copy of the spreadsheet of data for example just change my #s and see if you can see what you can do to get this going. Thanks again for your help Sean "Jon Peltier" wrote: Here's that development project I alluded to. You need to change the interface, replacing the simple inputbox with a userform to list the available dates. Then the code has to somehow examine the data and parse out just what you need. Or... You could set up a filter on your data, and use it to display the date you want, and hide the rest. By default, data in hidden cells do not appear in the chart. That's if the dates are in a single column, with the data lined up in sequential rows. If you have different columns for each date's data, use something like this: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Sounds great ya I was trying out a few things at work and I can easily change the chart type by changing the default and the basic outlook of the chart just in the VBE, going to be nice to learn all of it. Only thing is the options I like with the popup asking where the chart to be put but when it asks for where the data is from I want to be able to type say May 12 and have 4 charts come up with the data I have in my system from May 12. Like for example one chart would be my cash/charge sales, one would be my accounts opened/closed, one would be $/contract compared to last year and finally my Month to Date sales compared to last years. Those are the 4 charts I want to create based on just entering in a date. I am thinking once I get it going I'll probably have to in the code write alot of IF='s right? like basically if=May 12 then it knows where to find the data and then create 4 charts out of it? Thanks "Jon Peltier" wrote: The procedure as posted is pretty simple. It can certainly be made as complicated as you'd like, changing the chart type (I think it uses the default, so you could change your default to make different types), even making multiple charts from a single selected range. Without knowing details, it's not easy to make specific recommendations, and of course, this starts to become a development project once it starts to grow custom features. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hey Jon, Wow thanks for those links. The one I found that will work the best is the Interactive Chart Creation as anyone can pick where they want the chart to go and what data boom done. Few questions though. It does come up as a built-in chart style which I would prefer either a column or pie chart instead is there a way in the macro/vbe to change so instead of the built-in one thats in there to make a different kind of chart instead so I don't have to change the chart type after its created. As well for my weekly report I do end up having around 4 different charts plotting 2 stats on each. Is there a way with this interactive setup to generate 4 different charts at one time like that instead of doing one and then just following the same set up and doing it again to 3 more? Thanks again "Jon Peltier" wrote: I don't think a macro would be any easier than setting up a worksheet- or formula-based solution, especially if you're not familiar with VBA. There are no "easy macro setups" if you don't know how to set up the problem. VBA isn't magic, it only does what Excel can do, but allows you to do it automatically. I suggest you refer to the tutorial I cited, perhaps after reading about dynamic charts and defined names. Dynamic Charts: http://peltiertech.com/Excel/Charts/Dynamics.html http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/Excel/Charts/DynamicLast12.html http://peltiertech.com/Excel/Charts/...hartLinks.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... And BTW would it be easier to be creating a macro for this setup or no?.. If so whats the best place I can go to to find out an easy macro setup? "Sean Moor" wrote: I am creating a Dashboard report for my Accounting department at work and I have a weekly one I set up with links for the series of a chart to another worksheet. Easy. But what I am trying to do is instead of creating a new one each week by changing the link location for the data is there a way I can just enter in a range of dates and the charts automatically change and filer in the new data without me having to change the link in the series where the data entry is coming from?? |
#13
|
|||
|
|||
Can charts automatically change by entering new range of dates
Yes I have and I thoroughly read it over but wasn't able to get done what was
explained. But I will try that again today and get back to you. Although it still doesn't allow me to create multiple charts of different data at once like I was planning just one chart at a time. Cause say for example I have Cash Sales/Charge Sales, $/Contract 08/$/Contract 07, Opened/Closed Contracts, MTD Sales 08/MTD Sales 07. Each of those I want graphed to each other compared so all together 4 graphs. So say I use that user interface and type in May 10 Start Date and May 18 End Date I want the chart to be able to make 4 separate charts for the Data in those dates. And yes the Data is all in one row to make it easier but I would assume I would have to enter in where the Data is located on the spreadsheet and what columns contain that data? Cause in each date there is also other information that I don't want to be charted. Thanks "Jon Peltier" wrote: Have you seen this article? http://pubs.logicalexpressions.com/P...cle.asp?ID=246 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hi Jon, Actually the first part of your email would be exactly what I want to do. Be able to instead of having the popup box to ask where the data is coming from instead to just have either a popup asking what Day(s) you want to chart or even a drop down menu with dates listed for a starting date and say another for end date or just one doesn't matter. I was able to get the macro going and change for my chart default look so that was good but just unable to get it to chart by user entered date instead of the usual just point and click on the data. As well as it is a setup to make one chart but I do need to make 4 charts and preferably just at once. So basically just say enter in May 10-May 18 and have the chart automatically know where the data for those dates is and make 4 separate charts all together. If thats not possible I could always just do it 4 separate times just trying to make it easier on people at work to be able to go in there themselves and do it as easy as possible. If you want I can send you a copy of the spreadsheet of data for example just change my #s and see if you can see what you can do to get this going. Thanks again for your help Sean "Jon Peltier" wrote: Here's that development project I alluded to. You need to change the interface, replacing the simple inputbox with a userform to list the available dates. Then the code has to somehow examine the data and parse out just what you need. Or... You could set up a filter on your data, and use it to display the date you want, and hide the rest. By default, data in hidden cells do not appear in the chart. That's if the dates are in a single column, with the data lined up in sequential rows. If you have different columns for each date's data, use something like this: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Sounds great ya I was trying out a few things at work and I can easily change the chart type by changing the default and the basic outlook of the chart just in the VBE, going to be nice to learn all of it. Only thing is the options I like with the popup asking where the chart to be put but when it asks for where the data is from I want to be able to type say May 12 and have 4 charts come up with the data I have in my system from May 12. Like for example one chart would be my cash/charge sales, one would be my accounts opened/closed, one would be $/contract compared to last year and finally my Month to Date sales compared to last years. Those are the 4 charts I want to create based on just entering in a date. I am thinking once I get it going I'll probably have to in the code write alot of IF='s right? like basically if=May 12 then it knows where to find the data and then create 4 charts out of it? Thanks "Jon Peltier" wrote: The procedure as posted is pretty simple. It can certainly be made as complicated as you'd like, changing the chart type (I think it uses the default, so you could change your default to make different types), even making multiple charts from a single selected range. Without knowing details, it's not easy to make specific recommendations, and of course, this starts to become a development project once it starts to grow custom features. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hey Jon, Wow thanks for those links. The one I found that will work the best is the Interactive Chart Creation as anyone can pick where they want the chart to go and what data boom done. Few questions though. It does come up as a built-in chart style which I would prefer either a column or pie chart instead is there a way in the macro/vbe to change so instead of the built-in one thats in there to make a different kind of chart instead so I don't have to change the chart type after its created. As well for my weekly report I do end up having around 4 different charts plotting 2 stats on each. Is there a way with this interactive setup to generate 4 different charts at one time like that instead of doing one and then just following the same set up and doing it again to 3 more? Thanks again "Jon Peltier" wrote: I don't think a macro would be any easier than setting up a worksheet- or formula-based solution, especially if you're not familiar with VBA. There are no "easy macro setups" if you don't know how to set up the problem. VBA isn't magic, it only does what Excel can do, but allows you to do it automatically. I suggest you refer to the tutorial I cited, perhaps after reading about dynamic charts and defined names. Dynamic Charts: http://peltiertech.com/Excel/Charts/Dynamics.html http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/Excel/Charts/DynamicLast12.html http://peltiertech.com/Excel/Charts/...hartLinks.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... And BTW would it be easier to be creating a macro for this setup or no?.. If so whats the best place I can go to to find out an easy macro setup? "Sean Moor" wrote: I am creating a Dashboard report for my Accounting department at work and I have a weekly one I set up with links for the series of a chart to another worksheet. Easy. But what I am trying to do is instead of creating a new one each week by changing the link location for the data is there a way I can just enter in a range of dates and the charts automatically change and filer in the new data without me having to change the link in the series where the data entry is coming from?? |
#14
|
|||
|
|||
Can charts automatically change by entering new range of dates
Any news Jon?
If maybe emailing you my spreadsheet of data and the charts I want to look like that might help if you have a few mintues you can look it over and see kind of where I am getting at and exactly what I am tryingt o do because I have read over all the websites and pulling my hair out here to try to figure it out "Sean Moor" wrote: Yes I have and I thoroughly read it over but wasn't able to get done what was explained. But I will try that again today and get back to you. Although it still doesn't allow me to create multiple charts of different data at once like I was planning just one chart at a time. Cause say for example I have Cash Sales/Charge Sales, $/Contract 08/$/Contract 07, Opened/Closed Contracts, MTD Sales 08/MTD Sales 07. Each of those I want graphed to each other compared so all together 4 graphs. So say I use that user interface and type in May 10 Start Date and May 18 End Date I want the chart to be able to make 4 separate charts for the Data in those dates. And yes the Data is all in one row to make it easier but I would assume I would have to enter in where the Data is located on the spreadsheet and what columns contain that data? Cause in each date there is also other information that I don't want to be charted. Thanks "Jon Peltier" wrote: Have you seen this article? http://pubs.logicalexpressions.com/P...cle.asp?ID=246 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hi Jon, Actually the first part of your email would be exactly what I want to do. Be able to instead of having the popup box to ask where the data is coming from instead to just have either a popup asking what Day(s) you want to chart or even a drop down menu with dates listed for a starting date and say another for end date or just one doesn't matter. I was able to get the macro going and change for my chart default look so that was good but just unable to get it to chart by user entered date instead of the usual just point and click on the data. As well as it is a setup to make one chart but I do need to make 4 charts and preferably just at once. So basically just say enter in May 10-May 18 and have the chart automatically know where the data for those dates is and make 4 separate charts all together. If thats not possible I could always just do it 4 separate times just trying to make it easier on people at work to be able to go in there themselves and do it as easy as possible. If you want I can send you a copy of the spreadsheet of data for example just change my #s and see if you can see what you can do to get this going. Thanks again for your help Sean "Jon Peltier" wrote: Here's that development project I alluded to. You need to change the interface, replacing the simple inputbox with a userform to list the available dates. Then the code has to somehow examine the data and parse out just what you need. Or... You could set up a filter on your data, and use it to display the date you want, and hide the rest. By default, data in hidden cells do not appear in the chart. That's if the dates are in a single column, with the data lined up in sequential rows. If you have different columns for each date's data, use something like this: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Sounds great ya I was trying out a few things at work and I can easily change the chart type by changing the default and the basic outlook of the chart just in the VBE, going to be nice to learn all of it. Only thing is the options I like with the popup asking where the chart to be put but when it asks for where the data is from I want to be able to type say May 12 and have 4 charts come up with the data I have in my system from May 12. Like for example one chart would be my cash/charge sales, one would be my accounts opened/closed, one would be $/contract compared to last year and finally my Month to Date sales compared to last years. Those are the 4 charts I want to create based on just entering in a date. I am thinking once I get it going I'll probably have to in the code write alot of IF='s right? like basically if=May 12 then it knows where to find the data and then create 4 charts out of it? Thanks "Jon Peltier" wrote: The procedure as posted is pretty simple. It can certainly be made as complicated as you'd like, changing the chart type (I think it uses the default, so you could change your default to make different types), even making multiple charts from a single selected range. Without knowing details, it's not easy to make specific recommendations, and of course, this starts to become a development project once it starts to grow custom features. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hey Jon, Wow thanks for those links. The one I found that will work the best is the Interactive Chart Creation as anyone can pick where they want the chart to go and what data boom done. Few questions though. It does come up as a built-in chart style which I would prefer either a column or pie chart instead is there a way in the macro/vbe to change so instead of the built-in one thats in there to make a different kind of chart instead so I don't have to change the chart type after its created. As well for my weekly report I do end up having around 4 different charts plotting 2 stats on each. Is there a way with this interactive setup to generate 4 different charts at one time like that instead of doing one and then just following the same set up and doing it again to 3 more? Thanks again "Jon Peltier" wrote: I don't think a macro would be any easier than setting up a worksheet- or formula-based solution, especially if you're not familiar with VBA. There are no "easy macro setups" if you don't know how to set up the problem. VBA isn't magic, it only does what Excel can do, but allows you to do it automatically. I suggest you refer to the tutorial I cited, perhaps after reading about dynamic charts and defined names. Dynamic Charts: http://peltiertech.com/Excel/Charts/Dynamics.html http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/Excel/Charts/DynamicLast12.html http://peltiertech.com/Excel/Charts/...hartLinks.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... And BTW would it be easier to be creating a macro for this setup or no?.. If so whats the best place I can go to to find out an easy macro setup? "Sean Moor" wrote: I am creating a Dashboard report for my Accounting department at work and I have a weekly one I set up with links for the series of a chart to another worksheet. Easy. But what I am trying to do is instead of creating a new one each week by changing the link location for the data is there a way I can just enter in a range of dates and the charts automatically change and filer in the new data without me having to change the link in the series where the data entry is coming from?? |
#15
|
|||
|
|||
Can charts automatically change by entering new range of dates
Hey Jon I finally got it done. I was able to create the Dynamic Charting by
Dates from your website I was just missing a few points I never saw before and finally got it going. One question though about it. What if in AllDates I want to include more than one month. Each month in my spreadsheet has its own tab is there a way to include multiple tabs in 1 spreadsheet to the defined name of AllDates? "Sean Moor" wrote: Any news Jon? If maybe emailing you my spreadsheet of data and the charts I want to look like that might help if you have a few mintues you can look it over and see kind of where I am getting at and exactly what I am tryingt o do because I have read over all the websites and pulling my hair out here to try to figure it out "Sean Moor" wrote: Yes I have and I thoroughly read it over but wasn't able to get done what was explained. But I will try that again today and get back to you. Although it still doesn't allow me to create multiple charts of different data at once like I was planning just one chart at a time. Cause say for example I have Cash Sales/Charge Sales, $/Contract 08/$/Contract 07, Opened/Closed Contracts, MTD Sales 08/MTD Sales 07. Each of those I want graphed to each other compared so all together 4 graphs. So say I use that user interface and type in May 10 Start Date and May 18 End Date I want the chart to be able to make 4 separate charts for the Data in those dates. And yes the Data is all in one row to make it easier but I would assume I would have to enter in where the Data is located on the spreadsheet and what columns contain that data? Cause in each date there is also other information that I don't want to be charted. Thanks "Jon Peltier" wrote: Have you seen this article? http://pubs.logicalexpressions.com/P...cle.asp?ID=246 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hi Jon, Actually the first part of your email would be exactly what I want to do. Be able to instead of having the popup box to ask where the data is coming from instead to just have either a popup asking what Day(s) you want to chart or even a drop down menu with dates listed for a starting date and say another for end date or just one doesn't matter. I was able to get the macro going and change for my chart default look so that was good but just unable to get it to chart by user entered date instead of the usual just point and click on the data. As well as it is a setup to make one chart but I do need to make 4 charts and preferably just at once. So basically just say enter in May 10-May 18 and have the chart automatically know where the data for those dates is and make 4 separate charts all together. If thats not possible I could always just do it 4 separate times just trying to make it easier on people at work to be able to go in there themselves and do it as easy as possible. If you want I can send you a copy of the spreadsheet of data for example just change my #s and see if you can see what you can do to get this going. Thanks again for your help Sean "Jon Peltier" wrote: Here's that development project I alluded to. You need to change the interface, replacing the simple inputbox with a userform to list the available dates. Then the code has to somehow examine the data and parse out just what you need. Or... You could set up a filter on your data, and use it to display the date you want, and hide the rest. By default, data in hidden cells do not appear in the chart. That's if the dates are in a single column, with the data lined up in sequential rows. If you have different columns for each date's data, use something like this: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Sounds great ya I was trying out a few things at work and I can easily change the chart type by changing the default and the basic outlook of the chart just in the VBE, going to be nice to learn all of it. Only thing is the options I like with the popup asking where the chart to be put but when it asks for where the data is from I want to be able to type say May 12 and have 4 charts come up with the data I have in my system from May 12. Like for example one chart would be my cash/charge sales, one would be my accounts opened/closed, one would be $/contract compared to last year and finally my Month to Date sales compared to last years. Those are the 4 charts I want to create based on just entering in a date. I am thinking once I get it going I'll probably have to in the code write alot of IF='s right? like basically if=May 12 then it knows where to find the data and then create 4 charts out of it? Thanks "Jon Peltier" wrote: The procedure as posted is pretty simple. It can certainly be made as complicated as you'd like, changing the chart type (I think it uses the default, so you could change your default to make different types), even making multiple charts from a single selected range. Without knowing details, it's not easy to make specific recommendations, and of course, this starts to become a development project once it starts to grow custom features. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hey Jon, Wow thanks for those links. The one I found that will work the best is the Interactive Chart Creation as anyone can pick where they want the chart to go and what data boom done. Few questions though. It does come up as a built-in chart style which I would prefer either a column or pie chart instead is there a way in the macro/vbe to change so instead of the built-in one thats in there to make a different kind of chart instead so I don't have to change the chart type after its created. As well for my weekly report I do end up having around 4 different charts plotting 2 stats on each. Is there a way with this interactive setup to generate 4 different charts at one time like that instead of doing one and then just following the same set up and doing it again to 3 more? Thanks again "Jon Peltier" wrote: I don't think a macro would be any easier than setting up a worksheet- or formula-based solution, especially if you're not familiar with VBA. There are no "easy macro setups" if you don't know how to set up the problem. VBA isn't magic, it only does what Excel can do, but allows you to do it automatically. I suggest you refer to the tutorial I cited, perhaps after reading about dynamic charts and defined names. Dynamic Charts: http://peltiertech.com/Excel/Charts/Dynamics.html http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/Excel/Charts/DynamicLast12.html http://peltiertech.com/Excel/Charts/...hartLinks.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... And BTW would it be easier to be creating a macro for this setup or no?.. If so whats the best place I can go to to find out an easy macro setup? "Sean Moor" wrote: I am creating a Dashboard report for my Accounting department at work and I have a weekly one I set up with links for the series of a chart to another worksheet. Easy. But what I am trying to do is instead of creating a new one each week by changing the link location for the data is there a way I can just enter in a range of dates and the charts automatically change and filer in the new data without me having to change the link in the series where the data entry is coming from?? |
#16
|
|||
|
|||
Can charts automatically change by entering new range of dates
To work as described, the dynamic names must each be contained within a
single sheet (not necessarily the same as other dynamic names). In addition, a chart's source data can only under some circumstances be drawn from multiple sheets. All the X values must come from one sheet, all the Y values from one sheet, and the cell containing the series name from one sheet (duh, it's one cell). This means a series can contain data from up to three sheets., but cannot be spread across sheets the way your data is. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hey Jon I finally got it done. I was able to create the Dynamic Charting by Dates from your website I was just missing a few points I never saw before and finally got it going. One question though about it. What if in AllDates I want to include more than one month. Each month in my spreadsheet has its own tab is there a way to include multiple tabs in 1 spreadsheet to the defined name of AllDates? "Sean Moor" wrote: Any news Jon? If maybe emailing you my spreadsheet of data and the charts I want to look like that might help if you have a few mintues you can look it over and see kind of where I am getting at and exactly what I am tryingt o do because I have read over all the websites and pulling my hair out here to try to figure it out "Sean Moor" wrote: Yes I have and I thoroughly read it over but wasn't able to get done what was explained. But I will try that again today and get back to you. Although it still doesn't allow me to create multiple charts of different data at once like I was planning just one chart at a time. Cause say for example I have Cash Sales/Charge Sales, $/Contract 08/$/Contract 07, Opened/Closed Contracts, MTD Sales 08/MTD Sales 07. Each of those I want graphed to each other compared so all together 4 graphs. So say I use that user interface and type in May 10 Start Date and May 18 End Date I want the chart to be able to make 4 separate charts for the Data in those dates. And yes the Data is all in one row to make it easier but I would assume I would have to enter in where the Data is located on the spreadsheet and what columns contain that data? Cause in each date there is also other information that I don't want to be charted. Thanks "Jon Peltier" wrote: Have you seen this article? http://pubs.logicalexpressions.com/P...cle.asp?ID=246 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hi Jon, Actually the first part of your email would be exactly what I want to do. Be able to instead of having the popup box to ask where the data is coming from instead to just have either a popup asking what Day(s) you want to chart or even a drop down menu with dates listed for a starting date and say another for end date or just one doesn't matter. I was able to get the macro going and change for my chart default look so that was good but just unable to get it to chart by user entered date instead of the usual just point and click on the data. As well as it is a setup to make one chart but I do need to make 4 charts and preferably just at once. So basically just say enter in May 10-May 18 and have the chart automatically know where the data for those dates is and make 4 separate charts all together. If thats not possible I could always just do it 4 separate times just trying to make it easier on people at work to be able to go in there themselves and do it as easy as possible. If you want I can send you a copy of the spreadsheet of data for example just change my #s and see if you can see what you can do to get this going. Thanks again for your help Sean "Jon Peltier" wrote: Here's that development project I alluded to. You need to change the interface, replacing the simple inputbox with a userform to list the available dates. Then the code has to somehow examine the data and parse out just what you need. Or... You could set up a filter on your data, and use it to display the date you want, and hide the rest. By default, data in hidden cells do not appear in the chart. That's if the dates are in a single column, with the data lined up in sequential rows. If you have different columns for each date's data, use something like this: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Sounds great ya I was trying out a few things at work and I can easily change the chart type by changing the default and the basic outlook of the chart just in the VBE, going to be nice to learn all of it. Only thing is the options I like with the popup asking where the chart to be put but when it asks for where the data is from I want to be able to type say May 12 and have 4 charts come up with the data I have in my system from May 12. Like for example one chart would be my cash/charge sales, one would be my accounts opened/closed, one would be $/contract compared to last year and finally my Month to Date sales compared to last years. Those are the 4 charts I want to create based on just entering in a date. I am thinking once I get it going I'll probably have to in the code write alot of IF='s right? like basically if=May 12 then it knows where to find the data and then create 4 charts out of it? Thanks "Jon Peltier" wrote: The procedure as posted is pretty simple. It can certainly be made as complicated as you'd like, changing the chart type (I think it uses the default, so you could change your default to make different types), even making multiple charts from a single selected range. Without knowing details, it's not easy to make specific recommendations, and of course, this starts to become a development project once it starts to grow custom features. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hey Jon, Wow thanks for those links. The one I found that will work the best is the Interactive Chart Creation as anyone can pick where they want the chart to go and what data boom done. Few questions though. It does come up as a built-in chart style which I would prefer either a column or pie chart instead is there a way in the macro/vbe to change so instead of the built-in one thats in there to make a different kind of chart instead so I don't have to change the chart type after its created. As well for my weekly report I do end up having around 4 different charts plotting 2 stats on each. Is there a way with this interactive setup to generate 4 different charts at one time like that instead of doing one and then just following the same set up and doing it again to 3 more? Thanks again "Jon Peltier" wrote: I don't think a macro would be any easier than setting up a worksheet- or formula-based solution, especially if you're not familiar with VBA. There are no "easy macro setups" if you don't know how to set up the problem. VBA isn't magic, it only does what Excel can do, but allows you to do it automatically. I suggest you refer to the tutorial I cited, perhaps after reading about dynamic charts and defined names. Dynamic Charts: http://peltiertech.com/Excel/Charts/Dynamics.html http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/Excel/Charts/DynamicLast12.html http://peltiertech.com/Excel/Charts/...hartLinks.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... And BTW would it be easier to be creating a macro for this setup or no?.. If so whats the best place I can go to to find out an easy macro setup? "Sean Moor" wrote: I am creating a Dashboard report for my Accounting department at work and I have a weekly one I set up with links for the series of a chart to another worksheet. Easy. But what I am trying to do is instead of creating a new one each week by changing the link location for the data is there a way I can just enter in a range of dates and the charts automatically change and filer in the new data without me having to change the link in the series where the data entry is coming from?? |
#17
|
|||
|
|||
Can charts automatically change by entering new range of dates
Aww thanks for the reply but that sucks ya. I was working on this for quite
sometime and finally have it from your site that I can do any date range I want for a single month but multiple months in 1 chart I can't since each month has its own tab on one spreadsheet. I did send you an example of what it looks like and what overall I am trying to do through email not sure if you got it or not. If you have or haven't is there anyway I can do this now when I want to graph multiple months if possible on one chart? For ex last 3 days in April and first 4 days in May. Thanks "Jon Peltier" wrote: To work as described, the dynamic names must each be contained within a single sheet (not necessarily the same as other dynamic names). In addition, a chart's source data can only under some circumstances be drawn from multiple sheets. All the X values must come from one sheet, all the Y values from one sheet, and the cell containing the series name from one sheet (duh, it's one cell). This means a series can contain data from up to three sheets., but cannot be spread across sheets the way your data is. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hey Jon I finally got it done. I was able to create the Dynamic Charting by Dates from your website I was just missing a few points I never saw before and finally got it going. One question though about it. What if in AllDates I want to include more than one month. Each month in my spreadsheet has its own tab is there a way to include multiple tabs in 1 spreadsheet to the defined name of AllDates? "Sean Moor" wrote: Any news Jon? If maybe emailing you my spreadsheet of data and the charts I want to look like that might help if you have a few mintues you can look it over and see kind of where I am getting at and exactly what I am tryingt o do because I have read over all the websites and pulling my hair out here to try to figure it out "Sean Moor" wrote: Yes I have and I thoroughly read it over but wasn't able to get done what was explained. But I will try that again today and get back to you. Although it still doesn't allow me to create multiple charts of different data at once like I was planning just one chart at a time. Cause say for example I have Cash Sales/Charge Sales, $/Contract 08/$/Contract 07, Opened/Closed Contracts, MTD Sales 08/MTD Sales 07. Each of those I want graphed to each other compared so all together 4 graphs. So say I use that user interface and type in May 10 Start Date and May 18 End Date I want the chart to be able to make 4 separate charts for the Data in those dates. And yes the Data is all in one row to make it easier but I would assume I would have to enter in where the Data is located on the spreadsheet and what columns contain that data? Cause in each date there is also other information that I don't want to be charted. Thanks "Jon Peltier" wrote: Have you seen this article? http://pubs.logicalexpressions.com/P...cle.asp?ID=246 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hi Jon, Actually the first part of your email would be exactly what I want to do. Be able to instead of having the popup box to ask where the data is coming from instead to just have either a popup asking what Day(s) you want to chart or even a drop down menu with dates listed for a starting date and say another for end date or just one doesn't matter. I was able to get the macro going and change for my chart default look so that was good but just unable to get it to chart by user entered date instead of the usual just point and click on the data. As well as it is a setup to make one chart but I do need to make 4 charts and preferably just at once. So basically just say enter in May 10-May 18 and have the chart automatically know where the data for those dates is and make 4 separate charts all together. If thats not possible I could always just do it 4 separate times just trying to make it easier on people at work to be able to go in there themselves and do it as easy as possible. If you want I can send you a copy of the spreadsheet of data for example just change my #s and see if you can see what you can do to get this going. Thanks again for your help Sean "Jon Peltier" wrote: Here's that development project I alluded to. You need to change the interface, replacing the simple inputbox with a userform to list the available dates. Then the code has to somehow examine the data and parse out just what you need. Or... You could set up a filter on your data, and use it to display the date you want, and hide the rest. By default, data in hidden cells do not appear in the chart. That's if the dates are in a single column, with the data lined up in sequential rows. If you have different columns for each date's data, use something like this: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Sounds great ya I was trying out a few things at work and I can easily change the chart type by changing the default and the basic outlook of the chart just in the VBE, going to be nice to learn all of it. Only thing is the options I like with the popup asking where the chart to be put but when it asks for where the data is from I want to be able to type say May 12 and have 4 charts come up with the data I have in my system from May 12. Like for example one chart would be my cash/charge sales, one would be my accounts opened/closed, one would be $/contract compared to last year and finally my Month to Date sales compared to last years. Those are the 4 charts I want to create based on just entering in a date. I am thinking once I get it going I'll probably have to in the code write alot of IF='s right? like basically if=May 12 then it knows where to find the data and then create 4 charts out of it? Thanks "Jon Peltier" wrote: The procedure as posted is pretty simple. It can certainly be made as complicated as you'd like, changing the chart type (I think it uses the default, so you could change your default to make different types), even making multiple charts from a single selected range. Without knowing details, it's not easy to make specific recommendations, and of course, this starts to become a development project once it starts to grow custom features. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hey Jon, Wow thanks for those links. The one I found that will work the best is the Interactive Chart Creation as anyone can pick where they want the chart to go and what data boom done. Few questions though. It does come up as a built-in chart style which I would prefer either a column or pie chart instead is there a way in the macro/vbe to change so instead of the built-in one thats in there to make a different kind of chart instead so I don't have to change the chart type after its created. As well for my weekly report I do end up having around 4 different charts plotting 2 stats on each. Is there a way with this interactive setup to generate 4 different charts at one time like that instead of doing one and then just following the same set up and doing it again to 3 more? Thanks again "Jon Peltier" wrote: I don't think a macro would be any easier than setting up a worksheet- or formula-based solution, especially if you're not familiar with VBA. There are no "easy macro setups" if you don't know how to set up the problem. VBA isn't magic, it only does what Excel can do, but allows you |
#18
|
|||
|
|||
Can charts automatically change by entering new range of dates
You can make a summary sheet that links to the ranges you want on the
monthly sheets. This link shows how to build one such summary; yours is likely to be shaped differently, but conceptually the protocol is the same: http://peltiertech.com/Excel/ChartsH...iffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Aww thanks for the reply but that sucks ya. I was working on this for quite sometime and finally have it from your site that I can do any date range I want for a single month but multiple months in 1 chart I can't since each month has its own tab on one spreadsheet. I did send you an example of what it looks like and what overall I am trying to do through email not sure if you got it or not. If you have or haven't is there anyway I can do this now when I want to graph multiple months if possible on one chart? For ex last 3 days in April and first 4 days in May. Thanks "Jon Peltier" wrote: To work as described, the dynamic names must each be contained within a single sheet (not necessarily the same as other dynamic names). In addition, a chart's source data can only under some circumstances be drawn from multiple sheets. All the X values must come from one sheet, all the Y values from one sheet, and the cell containing the series name from one sheet (duh, it's one cell). This means a series can contain data from up to three sheets., but cannot be spread across sheets the way your data is. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hey Jon I finally got it done. I was able to create the Dynamic Charting by Dates from your website I was just missing a few points I never saw before and finally got it going. One question though about it. What if in AllDates I want to include more than one month. Each month in my spreadsheet has its own tab is there a way to include multiple tabs in 1 spreadsheet to the defined name of AllDates? "Sean Moor" wrote: Any news Jon? If maybe emailing you my spreadsheet of data and the charts I want to look like that might help if you have a few mintues you can look it over and see kind of where I am getting at and exactly what I am tryingt o do because I have read over all the websites and pulling my hair out here to try to figure it out "Sean Moor" wrote: Yes I have and I thoroughly read it over but wasn't able to get done what was explained. But I will try that again today and get back to you. Although it still doesn't allow me to create multiple charts of different data at once like I was planning just one chart at a time. Cause say for example I have Cash Sales/Charge Sales, $/Contract 08/$/Contract 07, Opened/Closed Contracts, MTD Sales 08/MTD Sales 07. Each of those I want graphed to each other compared so all together 4 graphs. So say I use that user interface and type in May 10 Start Date and May 18 End Date I want the chart to be able to make 4 separate charts for the Data in those dates. And yes the Data is all in one row to make it easier but I would assume I would have to enter in where the Data is located on the spreadsheet and what columns contain that data? Cause in each date there is also other information that I don't want to be charted. Thanks "Jon Peltier" wrote: Have you seen this article? http://pubs.logicalexpressions.com/P...cle.asp?ID=246 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hi Jon, Actually the first part of your email would be exactly what I want to do. Be able to instead of having the popup box to ask where the data is coming from instead to just have either a popup asking what Day(s) you want to chart or even a drop down menu with dates listed for a starting date and say another for end date or just one doesn't matter. I was able to get the macro going and change for my chart default look so that was good but just unable to get it to chart by user entered date instead of the usual just point and click on the data. As well as it is a setup to make one chart but I do need to make 4 charts and preferably just at once. So basically just say enter in May 10-May 18 and have the chart automatically know where the data for those dates is and make 4 separate charts all together. If thats not possible I could always just do it 4 separate times just trying to make it easier on people at work to be able to go in there themselves and do it as easy as possible. If you want I can send you a copy of the spreadsheet of data for example just change my #s and see if you can see what you can do to get this going. Thanks again for your help Sean "Jon Peltier" wrote: Here's that development project I alluded to. You need to change the interface, replacing the simple inputbox with a userform to list the available dates. Then the code has to somehow examine the data and parse out just what you need. Or... You could set up a filter on your data, and use it to display the date you want, and hide the rest. By default, data in hidden cells do not appear in the chart. That's if the dates are in a single column, with the data lined up in sequential rows. If you have different columns for each date's data, use something like this: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Sounds great ya I was trying out a few things at work and I can easily change the chart type by changing the default and the basic outlook of the chart just in the VBE, going to be nice to learn all of it. Only thing is the options I like with the popup asking where the chart to be put but when it asks for where the data is from I want to be able to type say May 12 and have 4 charts come up with the data I have in my system from May 12. Like for example one chart would be my cash/charge sales, one would be my accounts opened/closed, one would be $/contract compared to last year and finally my Month to Date sales compared to last years. Those are the 4 charts I want to create based on just entering in a date. I am thinking once I get it going I'll probably have to in the code write alot of IF='s right? like basically if=May 12 then it knows where to find the data and then create 4 charts out of it? Thanks "Jon Peltier" wrote: The procedure as posted is pretty simple. It can certainly be made as complicated as you'd like, changing the chart type (I think it uses the default, so you could change your default to make different types), even making multiple charts from a single selected range. Without knowing details, it's not easy to make specific recommendations, and of course, this starts to become a development project once it starts to grow custom features. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Sean Moor" wrote in message ... Hey Jon, Wow thanks for those links. The one I found that will work the best is the Interactive Chart Creation as anyone can pick where they want the chart to go and what data boom done. Few questions though. It does come up as a built-in chart style which I would prefer either a column or pie chart instead is there a way in the macro/vbe to change so instead of the built-in one thats in there to make a different kind of chart instead so I don't have to change the chart type after its created. As well for my weekly report I do end up having around 4 different charts plotting 2 stats on each. Is there a way with this interactive setup to generate 4 different charts at one time like that instead of doing one and then just following the same set up and doing it again to 3 more? Thanks again "Jon Peltier" wrote: I don't think a macro would be any easier than setting up a worksheet- or formula-based solution, especially if you're not familiar with VBA. There are no "easy macro setups" if you don't know how to set up the problem. VBA isn't magic, it only does what Excel can do, but allows you |
|
Thread Tools | |
Display Modes | |
|
|