Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Overlay charts
I am looking to produce overlay charts and would like
confirmation of my own understanding of this topic. Previous posts have yielded some very helpful results (thanks Jon Peltier). Am I right in thinking that to produce overlay charts it is something that I have to code into the chart rather than it being a standard Excel menu option? From the previous replies I have got example code and I can handle that but I just wanted to make sure that was the way to go?? Thanks for your comments, Ian |
#2
|
|||
|
|||
Ian -
Do you mean you want to make a combination chart? The built in combination charts are not all-inclusive, but Excel allows you to construct your own combinations. Make a chart with all series of the same type, then change some series by selecting each in turn and choosing Chart Type from the Chart menu. Overlaying charts is a complicated process and usually unnecessary. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: I am looking to produce overlay charts and would like confirmation of my own understanding of this topic. Previous posts have yielded some very helpful results (thanks Jon Peltier). Am I right in thinking that to produce overlay charts it is something that I have to code into the chart rather than it being a standard Excel menu option? From the previous replies I have got example code and I can handle that but I just wanted to make sure that was the way to go?? Thanks for your comments, Ian |
#3
|
|||
|
|||
Jon,
Thanks for your reply, I'm a little confused now as to what I need. What I'm doing is this, I sample chemicals in a spectrophotometer and the data (Absorbance against Wavelength) are recorded in Excel. I then produce a graph of this data. I then want to be able to overlay a separate graph of a different chemicals' "signature trace" on top of this so I can get a visual display of the two chemicals. The axes would be the same in both cases. Do you think this is an Overlay or Combination chart? Thanks for your comments. Ian. "Jon Peltier" wrote: Ian - Do you mean you want to make a combination chart? The built in combination charts are not all-inclusive, but Excel allows you to construct your own combinations. Make a chart with all series of the same type, then change some series by selecting each in turn and choosing Chart Type from the Chart menu. Overlaying charts is a complicated process and usually unnecessary. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: I am looking to produce overlay charts and would like confirmation of my own understanding of this topic. Previous posts have yielded some very helpful results (thanks Jon Peltier). Am I right in thinking that to produce overlay charts it is something that I have to code into the chart rather than it being a standard Excel menu option? From the previous replies I have got example code and I can handle that but I just wanted to make sure that was the way to go?? Thanks for your comments, Ian |
#4
|
|||
|
|||
Ian -
This is even easier (when you know how)! It's a simple two series scatter chart. Start with the XY Scatter chart of the new data. Open the workbook with the comparison data. Select and copy the X and Y data, then select the chart, and use Paste Special from the Edit menu to add the data as a new series. Alternatively, with the second workbook open, select the chart, go to Source Data on the Chart menu, select the Series tab, click Add, and navigate to the other workbook and select the X and Y. The Windows menu works with this dialog open to allow you to switch windows. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Jon, Thanks for your reply, I'm a little confused now as to what I need. What I'm doing is this, I sample chemicals in a spectrophotometer and the data (Absorbance against Wavelength) are recorded in Excel. I then produce a graph of this data. I then want to be able to overlay a separate graph of a different chemicals' "signature trace" on top of this so I can get a visual display of the two chemicals. The axes would be the same in both cases. Do you think this is an Overlay or Combination chart? Thanks for your comments. Ian. "Jon Peltier" wrote: Ian - Do you mean you want to make a combination chart? The built in combination charts are not all-inclusive, but Excel allows you to construct your own combinations. Make a chart with all series of the same type, then change some series by selecting each in turn and choosing Chart Type from the Chart menu. Overlaying charts is a complicated process and usually unnecessary. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: I am looking to produce overlay charts and would like confirmation of my own understanding of this topic. Previous posts have yielded some very helpful results (thanks Jon Peltier). Am I right in thinking that to produce overlay charts it is something that I have to code into the chart rather than it being a standard Excel menu option? From the previous replies I have got example code and I can handle that but I just wanted to make sure that was the way to go?? Thanks for your comments, Ian |
#5
|
|||
|
|||
Thanks Jon,
You're right it is easy when you know how. The comparitive chemical data is being held in a separate Excel workbook (Library.xls) and I managed to create a macro to pull that data in and add it as a second series to the original chart as you suggest. It does run a little "rough" in that you can see stuff flashing by in the background. Can this be improred upon? Also as I expand the library of comparative data it would be nice to have a drop-down box that offers all the possibilities in the library, maybe as a list of the macros produced to pull in the different chemical data. Can you point me to some help for setting up such a drop down box (combobox or list) as the Excel help files have not really explained too much. Thanks again, Ian. "Jon Peltier" wrote: Ian - This is even easier (when you know how)! It's a simple two series scatter chart. Start with the XY Scatter chart of the new data. Open the workbook with the comparison data. Select and copy the X and Y data, then select the chart, and use Paste Special from the Edit menu to add the data as a new series. Alternatively, with the second workbook open, select the chart, go to Source Data on the Chart menu, select the Series tab, click Add, and navigate to the other workbook and select the X and Y. The Windows menu works with this dialog open to allow you to switch windows. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Jon, Thanks for your reply, I'm a little confused now as to what I need. What I'm doing is this, I sample chemicals in a spectrophotometer and the data (Absorbance against Wavelength) are recorded in Excel. I then produce a graph of this data. I then want to be able to overlay a separate graph of a different chemicals' "signature trace" on top of this so I can get a visual display of the two chemicals. The axes would be the same in both cases. Do you think this is an Overlay or Combination chart? Thanks for your comments. Ian. "Jon Peltier" wrote: Ian - Do you mean you want to make a combination chart? The built in combination charts are not all-inclusive, but Excel allows you to construct your own combinations. Make a chart with all series of the same type, then change some series by selecting each in turn and choosing Chart Type from the Chart menu. Overlaying charts is a complicated process and usually unnecessary. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: I am looking to produce overlay charts and would like confirmation of my own understanding of this topic. Previous posts have yielded some very helpful results (thanks Jon Peltier). Am I right in thinking that to produce overlay charts it is something that I have to code into the chart rather than it being a standard Excel menu option? From the previous replies I have got example code and I can handle that but I just wanted to make sure that was the way to go?? Thanks for your comments, Ian |
#6
|
|||
|
|||
Hi Ian -
To prevent the screen flashing, use this approach: Application.ScreenUpdating = False ' your code goes here Application.ScreenUpdating = True In addition to this, you can smooth things out by replacing Object.Select Selection.Method with this Object.Method The selection of objects is almost always unnecessary, and it takes time and causes the screen to need frequent redrawing. Do you want an in-sheet listbox, or are you building a userform? These use different types of list boxes. You could make use of my favorite resource, and enter "excel worksheet listbox" into an advanced Google search. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Thanks Jon, You're right it is easy when you know how. The comparitive chemical data is being held in a separate Excel workbook (Library.xls) and I managed to create a macro to pull that data in and add it as a second series to the original chart as you suggest. It does run a little "rough" in that you can see stuff flashing by in the background. Can this be improred upon? Also as I expand the library of comparative data it would be nice to have a drop-down box that offers all the possibilities in the library, maybe as a list of the macros produced to pull in the different chemical data. Can you point me to some help for setting up such a drop down box (combobox or list) as the Excel help files have not really explained too much. Thanks again, Ian. "Jon Peltier" wrote: Ian - This is even easier (when you know how)! It's a simple two series scatter chart. Start with the XY Scatter chart of the new data. Open the workbook with the comparison data. Select and copy the X and Y data, then select the chart, and use Paste Special from the Edit menu to add the data as a new series. Alternatively, with the second workbook open, select the chart, go to Source Data on the Chart menu, select the Series tab, click Add, and navigate to the other workbook and select the X and Y. The Windows menu works with this dialog open to allow you to switch windows. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Jon, Thanks for your reply, I'm a little confused now as to what I need. What I'm doing is this, I sample chemicals in a spectrophotometer and the data (Absorbance against Wavelength) are recorded in Excel. I then produce a graph of this data. I then want to be able to overlay a separate graph of a different chemicals' "signature trace" on top of this so I can get a visual display of the two chemicals. The axes would be the same in both cases. Do you think this is an Overlay or Combination chart? Thanks for your comments. Ian. "Jon Peltier" wrote: Ian - Do you mean you want to make a combination chart? The built in combination charts are not all-inclusive, but Excel allows you to construct your own combinations. Make a chart with all series of the same type, then change some series by selecting each in turn and choosing Chart Type from the Chart menu. Overlaying charts is a complicated process and usually unnecessary. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: I am looking to produce overlay charts and would like confirmation of my own understanding of this topic. Previous posts have yielded some very helpful results (thanks Jon Peltier). Am I right in thinking that to produce overlay charts it is something that I have to code into the chart rather than it being a standard Excel menu option? From the previous replies I have got example code and I can handle that but I just wanted to make sure that was the way to go?? Thanks for your comments, Ian |
#7
|
|||
|
|||
Hi Jon,
Great advice, thanks. The screen flashing has gone. As far as the listbox issue goes, I hadn't thought about building a userform but will follow the links you suggest and decide which way to go. Thanks again for all your help. Ian. "Jon Peltier" wrote: Hi Ian - To prevent the screen flashing, use this approach: Application.ScreenUpdating = False ' your code goes here Application.ScreenUpdating = True In addition to this, you can smooth things out by replacing Object.Select Selection.Method with this Object.Method The selection of objects is almost always unnecessary, and it takes time and causes the screen to need frequent redrawing. Do you want an in-sheet listbox, or are you building a userform? These use different types of list boxes. You could make use of my favorite resource, and enter "excel worksheet listbox" into an advanced Google search. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Thanks Jon, You're right it is easy when you know how. The comparitive chemical data is being held in a separate Excel workbook (Library.xls) and I managed to create a macro to pull that data in and add it as a second series to the original chart as you suggest. It does run a little "rough" in that you can see stuff flashing by in the background. Can this be improred upon? Also as I expand the library of comparative data it would be nice to have a drop-down box that offers all the possibilities in the library, maybe as a list of the macros produced to pull in the different chemical data. Can you point me to some help for setting up such a drop down box (combobox or list) as the Excel help files have not really explained too much. Thanks again, Ian. "Jon Peltier" wrote: Ian - This is even easier (when you know how)! It's a simple two series scatter chart. Start with the XY Scatter chart of the new data. Open the workbook with the comparison data. Select and copy the X and Y data, then select the chart, and use Paste Special from the Edit menu to add the data as a new series. Alternatively, with the second workbook open, select the chart, go to Source Data on the Chart menu, select the Series tab, click Add, and navigate to the other workbook and select the X and Y. The Windows menu works with this dialog open to allow you to switch windows. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Jon, Thanks for your reply, I'm a little confused now as to what I need. What I'm doing is this, I sample chemicals in a spectrophotometer and the data (Absorbance against Wavelength) are recorded in Excel. I then produce a graph of this data. I then want to be able to overlay a separate graph of a different chemicals' "signature trace" on top of this so I can get a visual display of the two chemicals. The axes would be the same in both cases. Do you think this is an Overlay or Combination chart? Thanks for your comments. Ian. "Jon Peltier" wrote: Ian - Do you mean you want to make a combination chart? The built in combination charts are not all-inclusive, but Excel allows you to construct your own combinations. Make a chart with all series of the same type, then change some series by selecting each in turn and choosing Chart Type from the Chart menu. Overlaying charts is a complicated process and usually unnecessary. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: I am looking to produce overlay charts and would like confirmation of my own understanding of this topic. Previous posts have yielded some very helpful results (thanks Jon Peltier). Am I right in thinking that to produce overlay charts it is something that I have to code into the chart rather than it being a standard Excel menu option? From the previous replies I have got example code and I can handle that but I just wanted to make sure that was the way to go?? Thanks for your comments, Ian |
#8
|
|||
|
|||
Ian -
I imagine that a Forms menu listbox in the worksheet is what you want. A toolbox on a userform is more versatile, but takes more to set up, particularly if you are relatively inexperienced. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Hi Jon, Great advice, thanks. The screen flashing has gone. As far as the listbox issue goes, I hadn't thought about building a userform but will follow the links you suggest and decide which way to go. Thanks again for all your help. Ian. "Jon Peltier" wrote: Hi Ian - To prevent the screen flashing, use this approach: Application.ScreenUpdating = False ' your code goes here Application.ScreenUpdating = True In addition to this, you can smooth things out by replacing Object.Select Selection.Method with this Object.Method The selection of objects is almost always unnecessary, and it takes time and causes the screen to need frequent redrawing. Do you want an in-sheet listbox, or are you building a userform? These use different types of list boxes. You could make use of my favorite resource, and enter "excel worksheet listbox" into an advanced Google search. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Thanks Jon, You're right it is easy when you know how. The comparitive chemical data is being held in a separate Excel workbook (Library.xls) and I managed to create a macro to pull that data in and add it as a second series to the original chart as you suggest. It does run a little "rough" in that you can see stuff flashing by in the background. Can this be improred upon? Also as I expand the library of comparative data it would be nice to have a drop-down box that offers all the possibilities in the library, maybe as a list of the macros produced to pull in the different chemical data. Can you point me to some help for setting up such a drop down box (combobox or list) as the Excel help files have not really explained too much. Thanks again, Ian. "Jon Peltier" wrote: Ian - This is even easier (when you know how)! It's a simple two series scatter chart. Start with the XY Scatter chart of the new data. Open the workbook with the comparison data. Select and copy the X and Y data, then select the chart, and use Paste Special from the Edit menu to add the data as a new series. Alternatively, with the second workbook open, select the chart, go to Source Data on the Chart menu, select the Series tab, click Add, and navigate to the other workbook and select the X and Y. The Windows menu works with this dialog open to allow you to switch windows. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Jon, Thanks for your reply, I'm a little confused now as to what I need. What I'm doing is this, I sample chemicals in a spectrophotometer and the data (Absorbance against Wavelength) are recorded in Excel. I then produce a graph of this data. I then want to be able to overlay a separate graph of a different chemicals' "signature trace" on top of this so I can get a visual display of the two chemicals. The axes would be the same in both cases. Do you think this is an Overlay or Combination chart? Thanks for your comments. Ian. "Jon Peltier" wrote: Ian - Do you mean you want to make a combination chart? The built in combination charts are not all-inclusive, but Excel allows you to construct your own combinations. Make a chart with all series of the same type, then change some series by selecting each in turn and choosing Chart Type from the Chart menu. Overlaying charts is a complicated process and usually unnecessary. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: I am looking to produce overlay charts and would like confirmation of my own understanding of this topic. Previous posts have yielded some very helpful results (thanks Jon Peltier). Am I right in thinking that to produce overlay charts it is something that I have to code into the chart rather than it being a standard Excel menu option? From the previous replies I have got example code and I can handle that but I just wanted to make sure that was the way to go?? Thanks for your comments, Ian |
#9
|
|||
|
|||
Jon,
Oh dear is my inexperience that obvious? Access is more my thing but I'm trying to get familiar with Excel. Having read around forms and list boxes I'm still a bit unsure about whether a list/combobox will do what I want. I have this file, Library.xls with a number of different worksheets. Each worksheet has data on different chemicals. In the test document I want a list/combobox that when clicked gives me a list of the worksheets in Library.xls and then brings that data in to overlay as we've previoulsy done. I guess that selecting the chemical in the list/combobox will run a macro that gets that data etc...it's just popluating the list with the names of the Library.xls worksheets. What do you think? I really appreciate your help, Ian. "Jon Peltier" wrote: Ian - I imagine that a Forms menu listbox in the worksheet is what you want. A toolbox on a userform is more versatile, but takes more to set up, particularly if you are relatively inexperienced. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Hi Jon, Great advice, thanks. The screen flashing has gone. As far as the listbox issue goes, I hadn't thought about building a userform but will follow the links you suggest and decide which way to go. Thanks again for all your help. Ian. "Jon Peltier" wrote: Hi Ian - To prevent the screen flashing, use this approach: Application.ScreenUpdating = False ' your code goes here Application.ScreenUpdating = True In addition to this, you can smooth things out by replacing Object.Select Selection.Method with this Object.Method The selection of objects is almost always unnecessary, and it takes time and causes the screen to need frequent redrawing. Do you want an in-sheet listbox, or are you building a userform? These use different types of list boxes. You could make use of my favorite resource, and enter "excel worksheet listbox" into an advanced Google search. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Thanks Jon, You're right it is easy when you know how. The comparitive chemical data is being held in a separate Excel workbook (Library.xls) and I managed to create a macro to pull that data in and add it as a second series to the original chart as you suggest. It does run a little "rough" in that you can see stuff flashing by in the background. Can this be improred upon? Also as I expand the library of comparative data it would be nice to have a drop-down box that offers all the possibilities in the library, maybe as a list of the macros produced to pull in the different chemical data. Can you point me to some help for setting up such a drop down box (combobox or list) as the Excel help files have not really explained too much. Thanks again, Ian. "Jon Peltier" wrote: Ian - This is even easier (when you know how)! It's a simple two series scatter chart. Start with the XY Scatter chart of the new data. Open the workbook with the comparison data. Select and copy the X and Y data, then select the chart, and use Paste Special from the Edit menu to add the data as a new series. Alternatively, with the second workbook open, select the chart, go to Source Data on the Chart menu, select the Series tab, click Add, and navigate to the other workbook and select the X and Y. The Windows menu works with this dialog open to allow you to switch windows. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Jon, Thanks for your reply, I'm a little confused now as to what I need. What I'm doing is this, I sample chemicals in a spectrophotometer and the data (Absorbance against Wavelength) are recorded in Excel. I then produce a graph of this data. I then want to be able to overlay a separate graph of a different chemicals' "signature trace" on top of this so I can get a visual display of the two chemicals. The axes would be the same in both cases. Do you think this is an Overlay or Combination chart? Thanks for your comments. Ian. "Jon Peltier" wrote: Ian - Do you mean you want to make a combination chart? The built in combination charts are not all-inclusive, but Excel allows you to construct your own combinations. Make a chart with all series of the same type, then change some series by selecting each in turn and choosing Chart Type from the Chart menu. Overlaying charts is a complicated process and usually unnecessary. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: I am looking to produce overlay charts and would like confirmation of my own understanding of this topic. Previous posts have yielded some very helpful results (thanks Jon Peltier). Am I right in thinking that to produce overlay charts it is something that I have to code into the chart rather than it being a standard Excel menu option? From the previous replies I have got example code and I can handle that but I just wanted to make sure that was the way to go?? Thanks for your comments, Ian |
#10
|
|||
|
|||
Ian -
I have made a sample file and placed it on my web site. The file has five sheets: Test, Hydrogen, Helium, Lithium, and Beryllium. All sheets have data in A1:B21. The sheet named Test also has other features. It has a chart with the Test data and one other series. There are two Forms Toolbar controls, a List Box and a Combo Box, both using the same Input Range (i.e., names of the other sheets) and Cell Link (which list element is selected). There is a range of data in P1:Q21 which is linked to one of the reference sheets using the INDIRECT worksheet function and the selected list element. It's not too heavily documented <g, but it follows a couple pages on my site: http://peltiertech.com/Excel/Charts/ChartByControl.html http://peltiertech.com/Excel/ChartsH...iffSheets.html The workbook is contained in this zip file: http://peltiertech.com/Excel/Zips/Ch...stStandard.zip - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Jon, Oh dear is my inexperience that obvious? Access is more my thing but I'm trying to get familiar with Excel. Having read around forms and list boxes I'm still a bit unsure about whether a list/combobox will do what I want. I have this file, Library.xls with a number of different worksheets. Each worksheet has data on different chemicals. In the test document I want a list/combobox that when clicked gives me a list of the worksheets in Library.xls and then brings that data in to overlay as we've previoulsy done. I guess that selecting the chemical in the list/combobox will run a macro that gets that data etc...it's just popluating the list with the names of the Library.xls worksheets. What do you think? I really appreciate your help, Ian. "Jon Peltier" wrote: Ian - I imagine that a Forms menu listbox in the worksheet is what you want. A toolbox on a userform is more versatile, but takes more to set up, particularly if you are relatively inexperienced. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Hi Jon, Great advice, thanks. The screen flashing has gone. As far as the listbox issue goes, I hadn't thought about building a userform but will follow the links you suggest and decide which way to go. Thanks again for all your help. Ian. "Jon Peltier" wrote: Hi Ian - To prevent the screen flashing, use this approach: Application.ScreenUpdating = False ' your code goes here Application.ScreenUpdating = True In addition to this, you can smooth things out by replacing Object.Select Selection.Method with this Object.Method The selection of objects is almost always unnecessary, and it takes time and causes the screen to need frequent redrawing. Do you want an in-sheet listbox, or are you building a userform? These use different types of list boxes. You could make use of my favorite resource, and enter "excel worksheet listbox" into an advanced Google search. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Thanks Jon, You're right it is easy when you know how. The comparitive chemical data is being held in a separate Excel workbook (Library.xls) and I managed to create a macro to pull that data in and add it as a second series to the original chart as you suggest. It does run a little "rough" in that you can see stuff flashing by in the background. Can this be improred upon? Also as I expand the library of comparative data it would be nice to have a drop-down box that offers all the possibilities in the library, maybe as a list of the macros produced to pull in the different chemical data. Can you point me to some help for setting up such a drop down box (combobox or list) as the Excel help files have not really explained too much. Thanks again, Ian. "Jon Peltier" wrote: Ian - This is even easier (when you know how)! It's a simple two series scatter chart. Start with the XY Scatter chart of the new data. Open the workbook with the comparison data. Select and copy the X and Y data, then select the chart, and use Paste Special from the Edit menu to add the data as a new series. Alternatively, with the second workbook open, select the chart, go to Source Data on the Chart menu, select the Series tab, click Add, and navigate to the other workbook and select the X and Y. The Windows menu works with this dialog open to allow you to switch windows. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Jon, Thanks for your reply, I'm a little confused now as to what I need. What I'm doing is this, I sample chemicals in a spectrophotometer and the data (Absorbance against Wavelength) are recorded in Excel. I then produce a graph of this data. I then want to be able to overlay a separate graph of a different chemicals' "signature trace" on top of this so I can get a visual display of the two chemicals. The axes would be the same in both cases. Do you think this is an Overlay or Combination chart? Thanks for your comments. Ian. "Jon Peltier" wrote: Ian - Do you mean you want to make a combination chart? The built in combination charts are not all-inclusive, but Excel allows you to construct your own combinations. Make a chart with all series of the same type, then change some series by selecting each in turn and choosing Chart Type from the Chart menu. Overlaying charts is a complicated process and usually unnecessary. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: I am looking to produce overlay charts and would like confirmation of my own understanding of this topic. Previous posts have yielded some very helpful results (thanks Jon Peltier). Am I right in thinking that to produce overlay charts it is something that I have to code into the chart rather than it being a standard Excel menu option? From the previous replies I have got example code and I can handle that but I just wanted to make sure that was the way to go?? Thanks for your comments, Ian |
#11
|
|||
|
|||
Hi Jon,
Neat, very neat! That's just what I'm looking for and not a line of code in sight. I followed through your explanations on the pages of your site and was able to follow your thinking perfectly. I guess that if you want to point to a worksheet in a different Excel file you just need to construct the INDIRECT function to reflect it's path? Just out of interest, do you know how many worksheets you can have in a workbook? Your help to me has been most appreciated, thank you very much indeed. Ian. "Jon Peltier" wrote: Ian - I have made a sample file and placed it on my web site. The file has five sheets: Test, Hydrogen, Helium, Lithium, and Beryllium. All sheets have data in A1:B21. The sheet named Test also has other features. It has a chart with the Test data and one other series. There are two Forms Toolbar controls, a List Box and a Combo Box, both using the same Input Range (i.e., names of the other sheets) and Cell Link (which list element is selected). There is a range of data in P1:Q21 which is linked to one of the reference sheets using the INDIRECT worksheet function and the selected list element. It's not too heavily documented <g, but it follows a couple pages on my site: http://peltiertech.com/Excel/Charts/ChartByControl.html http://peltiertech.com/Excel/ChartsH...iffSheets.html The workbook is contained in this zip file: http://peltiertech.com/Excel/Zips/Ch...stStandard.zip - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Jon, Oh dear is my inexperience that obvious? Access is more my thing but I'm trying to get familiar with Excel. Having read around forms and list boxes I'm still a bit unsure about whether a list/combobox will do what I want. I have this file, Library.xls with a number of different worksheets. Each worksheet has data on different chemicals. In the test document I want a list/combobox that when clicked gives me a list of the worksheets in Library.xls and then brings that data in to overlay as we've previoulsy done. I guess that selecting the chemical in the list/combobox will run a macro that gets that data etc...it's just popluating the list with the names of the Library.xls worksheets. What do you think? I really appreciate your help, Ian. "Jon Peltier" wrote: Ian - I imagine that a Forms menu listbox in the worksheet is what you want. A toolbox on a userform is more versatile, but takes more to set up, particularly if you are relatively inexperienced. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Hi Jon, Great advice, thanks. The screen flashing has gone. As far as the listbox issue goes, I hadn't thought about building a userform but will follow the links you suggest and decide which way to go. Thanks again for all your help. Ian. "Jon Peltier" wrote: Hi Ian - To prevent the screen flashing, use this approach: Application.ScreenUpdating = False ' your code goes here Application.ScreenUpdating = True In addition to this, you can smooth things out by replacing Object.Select Selection.Method with this Object.Method The selection of objects is almost always unnecessary, and it takes time and causes the screen to need frequent redrawing. Do you want an in-sheet listbox, or are you building a userform? These use different types of list boxes. You could make use of my favorite resource, and enter "excel worksheet listbox" into an advanced Google search. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Thanks Jon, You're right it is easy when you know how. The comparitive chemical data is being held in a separate Excel workbook (Library.xls) and I managed to create a macro to pull that data in and add it as a second series to the original chart as you suggest. It does run a little "rough" in that you can see stuff flashing by in the background. Can this be improred upon? Also as I expand the library of comparative data it would be nice to have a drop-down box that offers all the possibilities in the library, maybe as a list of the macros produced to pull in the different chemical data. Can you point me to some help for setting up such a drop down box (combobox or list) as the Excel help files have not really explained too much. Thanks again, Ian. "Jon Peltier" wrote: Ian - This is even easier (when you know how)! It's a simple two series scatter chart. Start with the XY Scatter chart of the new data. Open the workbook with the comparison data. Select and copy the X and Y data, then select the chart, and use Paste Special from the Edit menu to add the data as a new series. Alternatively, with the second workbook open, select the chart, go to Source Data on the Chart menu, select the Series tab, click Add, and navigate to the other workbook and select the X and Y. The Windows menu works with this dialog open to allow you to switch windows. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: Jon, Thanks for your reply, I'm a little confused now as to what I need. What I'm doing is this, I sample chemicals in a spectrophotometer and the data (Absorbance against Wavelength) are recorded in Excel. I then produce a graph of this data. I then want to be able to overlay a separate graph of a different chemicals' "signature trace" on top of this so I can get a visual display of the two chemicals. The axes would be the same in both cases. Do you think this is an Overlay or Combination chart? Thanks for your comments. Ian. "Jon Peltier" wrote: Ian - Do you mean you want to make a combination chart? The built in combination charts are not all-inclusive, but Excel allows you to construct your own combinations. Make a chart with all series of the same type, then change some series by selecting each in turn and choosing Chart Type from the Chart menu. Overlaying charts is a complicated process and usually unnecessary. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Ian wrote: I am looking to produce overlay charts and would like confirmation of my own understanding of this topic. Previous posts have yielded some very helpful results (thanks Jon Peltier). Am I right in thinking that to produce overlay charts it is something that I have to code into the chart rather than it being a standard Excel menu option? From the previous replies I have got example code and I can handle that but I just wanted to make sure that was the way to go?? Thanks for your comments, Ian |
#12
|
|||
|
|||
Ian wrote: Hi Jon, Neat, very neat! That's just what I'm looking for and not a line of code in sight. I followed through your explanations on the pages of your site and was able to follow your thinking perfectly. Great! I guess that if you want to point to a worksheet in a different Excel file you just need to construct the INDIRECT function to reflect it's path? Correct. It's possible to reference cells in a closed workbook, but I have a nagging suspicion the address has to be hard coded, not constructed using INDIRECT. A quick test will show you the answer. If you keep the files open, no big deal. Just out of interest, do you know how many worksheets you can have in a workbook? Limited by available memory, which doesn't mean how many gigs of ram you have, but rather how much of it Excel is designed to use (relatively little by today's standards). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ |
#13
|
|||
|
|||
Jon, thanks again for all your help,
Cheers, Ian. "Jon Peltier" wrote: Ian wrote: Hi Jon, Neat, very neat! That's just what I'm looking for and not a line of code in sight. I followed through your explanations on the pages of your site and was able to follow your thinking perfectly. Great! I guess that if you want to point to a worksheet in a different Excel file you just need to construct the INDIRECT function to reflect it's path? Correct. It's possible to reference cells in a closed workbook, but I have a nagging suspicion the address has to be hard coded, not constructed using INDIRECT. A quick test will show you the answer. If you keep the files open, no big deal. Just out of interest, do you know how many worksheets you can have in a workbook? Limited by available memory, which doesn't mean how many gigs of ram you have, but rather how much of it Excel is designed to use (relatively little by today's standards). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ |
#14
|
|||
|
|||
Glad to help.
- Jon Ian wrote: Jon, thanks again for all your help, Cheers, Ian. "Jon Peltier" wrote: Ian wrote: Hi Jon, Neat, very neat! That's just what I'm looking for and not a line of code in sight. I followed through your explanations on the pages of your site and was able to follow your thinking perfectly. Great! I guess that if you want to point to a worksheet in a different Excel file you just need to construct the INDIRECT function to reflect it's path? Correct. It's possible to reference cells in a closed workbook, but I have a nagging suspicion the address has to be hard coded, not constructed using INDIRECT. A quick test will show you the answer. If you keep the files open, no big deal. Just out of interest, do you know how many worksheets you can have in a workbook? Limited by available memory, which doesn't mean how many gigs of ram you have, but rather how much of it Excel is designed to use (relatively little by today's standards). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can links between Excel 2003 Pivot Charts and their pivot table b. | Charts and Charting in Excel | |||
i need help automating, or at least simplifying, my charts | Charts and Charting in Excel | |||
How do I remove empty chart plots from linked worksheet charts | Charts and Charting in Excel | |||
initial size of new charts | Charts and Charting in Excel | |||
Macro for multiple charts | Excel Worksheet Functions |