Copy worksheet to 2nd workbook but have no reference to 1st workbo

[i4004]

I am necessarily (someone else's system) working with 12 plus different
workbooks which are badly formatted so that they can't be easily printed. So
I have created a 'sheet2' which has references to pick out the data I want,
arranged as I want it to print it.

However, when I copy one of these 'sheet2s' to another workbook, all of the
references e.g. =a1 have the reference to the first workbook e.g. =
[workbook1]Sheet1!A2

I am more than happy with the 'sheet1' part but don't want the [workbook1]
part as, of course, this won't work when I put it in [workbook2]!

Help please!

 

[Dave Peterson]

You could save the new workbook, then change the links via
edit|links|change source.

But I like this technique.
I change all the formulas to plain old text in the original worksheet:
Select all the cells
edit|replace
what: =
with: $$$$$=
replace all

Then do the copy|paste

Then go back to both the pasted and original worksheet and change my strings
back to formulas:
Edit|replace
what: $$$$$=
with: =
replace all

I am necessarily (someone else's system) working with 12 plus different
workbooks which are badly formatted so that they can't be easily printed. So
I have created a 'sheet2' which has references to pick out the data I want,
arranged as I want it to print it.

However, when I copy one of these 'sheet2s' to another workbook, all of the
references e.g. =a1 have the reference to the first workbook e.g. =
[workbook1]Sheet1!A2

I am more than happy with the 'sheet1' part but don't want the [workbook1]
part as, of course, this won't work when I put it in [workbook2]!

Help please!

 

[Don]

what about past / special / values? or have your sheet link to the original
one ?

 

[i4004]

Thanks for the link suggestion - naturally, it crashed my Excel! Tried to do
too much at once I expect.

I think I need to be more explicit in the nature of the problem. They are
school reports which have a table with 20 columns and 20 to 30 students as
rows.

Of those 20 columns I want just 4 non contiguous columns but most of the rows.

So, I have
Workbook1
sheet1 - is the bad worksheet
sheet2 - is the worksheet with refs sheet1!A1 and so one which get what I
want to print.

Now I don't want the faff of making up the 'referencing' sheet2 each time I
go to a new one of these report workbooks - I just want to be able to copy
the sheet2, with all of its formatting and references etc from workbook1 to
workbook2 and 3 and 4 ad infinitum.

Surely it is possible to flick a switch that just leaves the references as
sheet1!a1 rather than changing it to [workbook1]sheet1!a1 when moved to
workbook2?

Or maybe not - maybe I have to risk crashing it again to edit those links or
do the search and replace?
--
Regards

i4004

You could save the new workbook, then change the links via
edit|links|change source.

But I like this technique.
I change all the formulas to plain old text in the original worksheet:
Select all the cells
edit|replace
what: =
with: $$$$$=
replace all

Then do the copy|paste

Then go back to both the pasted and original worksheet and change my strings
back to formulas:
Edit|replace
what: $$$$$=
with: =
replace all

I am necessarily (someone else's system) working with 12 plus different
workbooks which are badly formatted so that they can't be easily printed. So
I have created a 'sheet2' which has references to pick out the data I want,
arranged as I want it to print it.

However, when I copy one of these 'sheet2s' to another workbook, all of the
references e.g. =a1 have the reference to the first workbook e.g. =
[workbook1]Sheet1!A2

I am more than happy with the 'sheet1' part but don't want the [workbook1]
part as, of course, this won't work when I put it in [workbook2]!

Help please!

 

[i4004]

Thanks Don but I want to be able to just move the worksheet as a tool from
one workbook to another. I will have a constant stream of new workbooks
coming my way into which I will need to plonk this 'reading and printing'
worksheet.

I just want those sheet1 references to stay 'sheet1!a1' and nothing else
without having to mess about - that is because there are 20 other people who
don't have much of a clue about Excel but face the same problem; it has to be
dead easy.

 

[i4004]

I have just succeeded in doing the 'edit links|change source' where I change
the source to a saved copy of itself - that works but seems rather a
roundabout way of doing what could have been coded into the program as on
option?

e.g. 'when you copy the worksheet between workbooks, hold down ALT and it
will not take the links back to the source'

What is looking better is saving the 'Printing worksheet' as a standalone
workbook, with the links to, say, Workbook1, and then just changing the
source to workbook2 and then 3 etc. Perhaps that was what you meant?

Part of my problem with links was that they are xlm rather than xls files
and the links to xlm are not recognised by my Excel2003 (why?).

 

[Gord Dibben]

Only Office 2003 Professional supports XLM

What edition are you running?


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I think you meant XML (not xlm).

I'm not sure what the OP really meant.

 

[Gord Dibben]

Yeah..............XML

Gord

I think you meant XML (not xlm).

I'm not sure what the OP really meant.