Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
James
 
Posts: n/a
Default 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   Report Post  
Jon Peltier
 
Posts: n/a
Default

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   Report Post  
James
 
Posts: n/a
Default

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   Report Post  
James
 
Posts: n/a
Default

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   Report Post  
Jon Peltier
 
Posts: n/a
Default

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   Report Post  
James
 
Posts: n/a
Default

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









  #7   Report Post  
Jon Peltier
 
Posts: n/a
Default

Metallurgy and Stats? Very eclectic!

Well, I used them (and Excel) in conjunction with my research activities
for a number of years. Then I took another job, where itturned out that
nobody else understood the stats (the director of engineering kept
asking me what "Standard Deviation" meant), and the metallurgy fell
victim to politics and mismanagement. But that's old news.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


James wrote:

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
hlink.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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Impoting data from Sheet 1 to Sheet 2 a-leano Excel Discussion (Misc queries) 1 April 20th 05 01:05 AM
referencing a sheet named in a cell then using data from that sheet gbeard Excel Worksheet Functions 4 April 15th 05 08:42 AM
Copying multiple sheets from one book 2 another and undertake spec Pank Mehta Excel Discussion (Misc queries) 14 March 16th 05 05:41 PM
Automatic cell increment with data from sheet 1 to sheet 2 Big G Excel Worksheet Functions 2 December 20th 04 06:59 PM
getting data from 2 different exc sheet pinar Excel Worksheet Functions 1 November 9th 04 03:58 PM


All times are GMT +1. The time now is 12:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"