pasting relative


R

rarewolf

How can I copy and paste from one spreadsheet to another without the
destination referencing the source? For example, I have 2 workbooks
with identical worksheets, but containing different data. One has
graphs in one sheet that references to data in other sheets ... how
can I copy the sheet (graphs) to the other workbook and have it
reference local data? I suppose I'm suspecting a special paste
operation (eg, alt+ctrl+v) ...

TIA :)
 
Ad

Advertisements

C

CLR

If you are trying to create a duplicate workbook with different data, just
open the file and save it under a new name......then change the data
according to the new conditions......the references will stay within that
book.


Vaya con Dios,
Chuck, CABGx3
 
G

Gord Dibben

One method.............

Select all cells in the source sheet and Edit>Replace

What: =

With: ^^^

Replace all.

Copy to other workbook.

Reverse the Edit>Find process.


Gord Dibben MS Excel MVP
 
R

rarewolf

If you are trying to create a duplicate workbook with different data, just
open the file and save it under a new name......then change the data
according to the new conditions......the references will stay within that
book.

Vaya con Dios,
Chuck, CABGx3






- Show quoted text -

Hmmmmm(?) ... I suppose that would work, but it seems a bit backward.
That is, I have a half dozen datasheets and only one summary and
charts' sheet. Ideally, it would be much more efficient to copy/paste
the summary sheet. If it helps, the data sheets are all named the
same, and I only need to lose the reference to the originating
workbook filename(?)
 
R

RagDyer

After you copy and/or move the WS to a new WB, you can edit the links "en
masse" if they're all linked to a single file.

With the WS open in the new location, from the Menu Bar:
<Edit> <Links>
In the "Source File" window you'll see the existing links in the sheet (may
be more then 1),
Click on (select) an old link that you wish to change,
Click on "Change Source",
And navigate to the file that's to be the new source,
Click on it, then <OK>.

If there are additional links to change also displayed in the "Source File",
just repeat the process until everything is revised.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
If you are trying to create a duplicate workbook with different data, just
open the file and save it under a new name......then change the data
according to the new conditions......the references will stay within that
book.

Vaya con Dios,
Chuck, CABGx3






- Show quoted text -

Hmmmmm(?) ... I suppose that would work, but it seems a bit backward.
That is, I have a half dozen datasheets and only one summary and
charts' sheet. Ideally, it would be much more efficient to copy/paste
the summary sheet. If it helps, the data sheets are all named the
same, and I only need to lose the reference to the originating
workbook filename(?)
 
R

rarewolf

After you copy and/or move the WS to a new WB, you can edit the links "en
masse" if they're all linked to a single file.

With the WS open in the new location, from the Menu Bar:
<Edit> <Links>
In the "Source File" window you'll see the existing links in the sheet (may
be more then 1),
Click on (select) an old link that you wish to change,
Click on "Change Source",
And navigate to the file that's to be the new source,
Click on it, then <OK>.

If there are additional links to change also displayed in the "Source File",
just repeat the process until everything is revised.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !






Hmmmmm(?) ... I suppose that would work, but it seems a bit backward.
That is, I have a half dozen datasheets and only one summary and
charts' sheet.  Ideally, it would be much more efficient to copy/paste
the summary sheet.  If it helps, the data sheets are all named the
same, and I only need to lose the reference to the originating
workbook filename(?)- Hide quoted text -

- Show quoted text -

"Edit=>Links" seems to work the way I want, but it doesn't seem to
find the references to data inside the Charts that I created(?) What
did I do wrong?

cheerios :blush:)
 
Ad

Advertisements

R

RagDyer

Bill Manville has a program that supposedly works wonders with links.
Folks around here recommend it highly:

http://www.oaltd.co.uk/MVP/Default.htm


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

After you copy and/or move the WS to a new WB, you can edit the links "en
masse" if they're all linked to a single file.

With the WS open in the new location, from the Menu Bar:
<Edit> <Links>
In the "Source File" window you'll see the existing links in the sheet
(may
be more then 1),
Click on (select) an old link that you wish to change,
Click on "Change Source",
And navigate to the file that's to be the new source,
Click on it, then <OK>.

If there are additional links to change also displayed in the "Source
File",
just repeat the process until everything is revised.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------"rarewolf"






Hmmmmm(?) ... I suppose that would work, but it seems a bit backward.
That is, I have a half dozen datasheets and only one summary and
charts' sheet. Ideally, it would be much more efficient to copy/paste
the summary sheet. If it helps, the data sheets are all named the
same, and I only need to lose the reference to the originating
workbook filename(?)- Hide quoted text -

- Show quoted text -

"Edit=>Links" seems to work the way I want, but it doesn't seem to
find the references to data inside the Charts that I created(?) What
did I do wrong?

cheerios :blush:)
 
R

rarewolf

Bill Manville has a program that supposedly works wonders with links.
Folks around here recommend it highly:

http://www.oaltd.co.uk/MVP/Default.htm

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------








"Edit=>Links" seems to work the way I want, but it doesn't seem to
find the references to data inside the Charts that I created(?)  What
did I do wrong?

cheerios  :blush:)- Hide quoted text -

- Show quoted text -

I was finally able to get this to work with 'Edit=>Links', but I first
needed to create small tables on the chart page, which essentionally
read the other datasheets (i.e., =[datasheet]!$D$14). It is these
tables which the charts refer to, and which can easily be changed with
'Edit=>Links'.

cheerios :blush:)
 
Ad

Advertisements

R

RagDyer

The archives will appreciate the info on how you were able to work it out.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Bill Manville has a program that supposedly works wonders with links.
Folks around here recommend it highly:

http://www.oaltd.co.uk/MVP/Default.htm

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------








"Edit=>Links" seems to work the way I want, but it doesn't seem to
find the references to data inside the Charts that I created(?) What
did I do wrong?

cheerios :blush:)- Hide quoted text -

- Show quoted text -

I was finally able to get this to work with 'Edit=>Links', but I first
needed to create small tables on the chart page, which essentionally
read the other datasheets (i.e., =[datasheet]!$D$14). It is these
tables which the charts refer to, and which can easily be changed with
'Edit=>Links'.

cheerios :blush:)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top