| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Using a relative SHEET reference for source data in a chart
Hello All,
I have a workbook with sheet 1 containing data, and sheet 2 containing charts based on the data in sheet 1. I want to be able to use these two sheets as templates for new sheets in the same workbook like so: sheet 1 data ------- sheet 2 chart sheet 3 data ------- sheet 4 chart sheet 5 data ------- sheet 6 chart etc. When I try to copy sheet 1 to sheet 3 all is well, but when I copy the charts in sheet 2 to sheet 4, those charts source data is still gathered from sheet 1. I need a method to replace the "sheet reference" in the source data to refer to the PREVIOUS sheet, not the original sheet. Hope that makes sense. I have looked everywhere in help as well as the MS site, and google-a-go-go with no luck. If you can help it would be greatly appreciated. JMA |
|
#2
|
|||
|
|||
|
James -
Make a file with two sheets, the data and the chart, and save it in a handy directory. Open this file, change the data, and move the sheets together into the main workbook. Reopen the file, update the data to the next values, and move the sheets into the main workbook. Etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ James wrote: Hello All, I have a workbook with sheet 1 containing data, and sheet 2 containing charts based on the data in sheet 1. I want to be able to use these two sheets as templates for new sheets in the same workbook like so: sheet 1 data ------- sheet 2 chart sheet 3 data ------- sheet 4 chart sheet 5 data ------- sheet 6 chart etc. When I try to copy sheet 1 to sheet 3 all is well, but when I copy the charts in sheet 2 to sheet 4, those charts source data is still gathered from sheet 1. I need a method to replace the "sheet reference" in the source data to refer to the PREVIOUS sheet, not the original sheet. Hope that makes sense. I have looked everywhere in help as well as the MS site, and google-a-go-go with no luck. If you can help it would be greatly appreciated. JMA |
|
#3
|
|||
|
|||
|
Jon,
Thanks for the reply. When I try the operation below, the copied sheets are moved, but the chart source data referes to the other file, not the main workbook? Am I missing something? Thanks again. JMA "Jon Peltier" wrote in message ... James - Make a file with two sheets, the data and the chart, and save it in a handy directory. Open this file, change the data, and move the sheets together into the main workbook. Reopen the file, update the data to the next values, and move the sheets into the main workbook. Etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ James wrote: Hello All, I have a workbook with sheet 1 containing data, and sheet 2 containing charts based on the data in sheet 1. I want to be able to use these two sheets as templates for new sheets in the same workbook like so: sheet 1 data ------- sheet 2 chart sheet 3 data ------- sheet 4 chart sheet 5 data ------- sheet 6 chart etc. When I try to copy sheet 1 to sheet 3 all is well, but when I copy the charts in sheet 2 to sheet 4, those charts source data is still gathered from sheet 1. I need a method to replace the "sheet reference" in the source data to refer to the PREVIOUS sheet, not the original sheet. Hope that makes sense. I have looked everywhere in help as well as the MS site, and google-a-go-go with no luck. If you can help it would be greatly appreciated. JMA |
|
#4
|
|||
|
|||
|
I got it. I was copying, and it appears you must MOVE the sheets to the
main workbook...as you said below. Thanks again! "James" wrote in message nk.net... Jon, Thanks for the reply. When I try the operation below, the copied sheets are moved, but the chart source data referes to the other file, not the main workbook? Am I missing something? Thanks again. JMA "Jon Peltier" wrote in message ... James - Make a file with two sheets, the data and the chart, and save it in a handy directory. Open this file, change the data, and move the sheets together into the main workbook. Reopen the file, update the data to the next values, and move the sheets into the main workbook. Etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ James wrote: Hello All, I have a workbook with sheet 1 containing data, and sheet 2 containing charts based on the data in sheet 1. I want to be able to use these two sheets as templates for new sheets in the same workbook like so: sheet 1 data ------- sheet 2 chart sheet 3 data ------- sheet 4 chart sheet 5 data ------- sheet 6 chart etc. When I try to copy sheet 1 to sheet 3 all is well, but when I copy the charts in sheet 2 to sheet 4, those charts source data is still gathered from sheet 1. I need a method to replace the "sheet reference" in the source data to refer to the PREVIOUS sheet, not the original sheet. Hope that makes sense. I have looked everywhere in help as well as the MS site, and google-a-go-go with no luck. If you can help it would be greatly appreciated. JMA |
|
#5
|
|||
|
|||
|
That's it. When you copy the sheets, the original sheet remains in the
original workbook. When you move the sheets, the original sheet moves to the new workbook. Fortunately, there is still a copy of the original workbook that you can reuse. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ James wrote: I got it. I was copying, and it appears you must MOVE the sheets to the main workbook...as you said below. Thanks again! "James" wrote in message nk.net... Jon, Thanks for the reply. When I try the operation below, the copied sheets are moved, but the chart source data referes to the other file, not the main workbook? Am I missing something? Thanks again. JMA "Jon Peltier" wrote in message ... James - Make a file with two sheets, the data and the chart, and save it in a handy directory. Open this file, change the data, and move the sheets together into the main workbook. Reopen the file, update the data to the next values, and move the sheets into the main workbook. Etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ James wrote: Hello All, I have a workbook with sheet 1 containing data, and sheet 2 containing charts based on the data in sheet 1. I want to be able to use these two sheets as templates for new sheets in the same workbook like so: sheet 1 data ------- sheet 2 chart sheet 3 data ------- sheet 4 chart sheet 5 data ------- sheet 6 chart etc. When I try to copy sheet 1 to sheet 3 all is well, but when I copy the charts in sheet 2 to sheet 4, those charts source data is still gathered from sheet 1. I need a method to replace the "sheet reference" in the source data to refer to the PREVIOUS sheet, not the original sheet. Hope that makes sense. I have looked everywhere in help as well as the MS site, and google-a-go-go with no luck. If you can help it would be greatly appreciated. JMA |
|
#6
|
|||
|
|||
|
It worked like a champ Jon, thanks again. Nice site too. I will be
certain to check in for the tutorials and other information. Metallurgy and Stats? Very eclectic! Regards, James! "Jon Peltier" wrote in message ... That's it. When you copy the sheets, the original sheet remains in the original workbook. When you move the sheets, the original sheet moves to the new workbook. Fortunately, there is still a copy of the original workbook that you can reuse. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ James wrote: I got it. I was copying, and it appears you must MOVE the sheets to the main workbook...as you said below. Thanks again! "James" wrote in message nk.net... Jon, Thanks for the reply. When I try the operation below, the copied sheets are moved, but the chart source data referes to the other file, not the main workbook? Am I missing something? Thanks again. JMA "Jon Peltier" wrote in message ... James - Make a file with two sheets, the data and the chart, and save it in a handy directory. Open this file, change the data, and move the sheets together into the main workbook. Reopen the file, update the data to the next values, and move the sheets into the main workbook. Etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ James wrote: Hello All, I have a workbook with sheet 1 containing data, and sheet 2 containing charts based on the data in sheet 1. I want to be able to use these two sheets as templates for new sheets in the same workbook like so: sheet 1 data ------- sheet 2 chart sheet 3 data ------- sheet 4 chart sheet 5 data ------- sheet 6 chart etc. When I try to copy sheet 1 to sheet 3 all is well, but when I copy the charts in sheet 2 to sheet 4, those charts source data is still gathered from sheet 1. I need a method to replace the "sheet reference" in the source data to refer to the PREVIOUS sheet, not the original sheet. Hope that makes sense. I have looked everywhere in help as well as the MS site, and google-a-go-go with no luck. If you can help it would be greatly appreciated. JMA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Impoting data from Sheet 1 to Sheet 2 | Excel Discussion (Misc queries) | |||
| referencing a sheet named in a cell then using data from that sheet | Excel Worksheet Functions | |||
| Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
| Automatic cell increment with data from sheet 1 to sheet 2 | Excel Worksheet Functions | |||
| getting data from 2 different exc sheet | Excel Worksheet Functions | |||
Hybrid Mode
