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!

--
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

i4004 wrote:
>
> 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!
>
> --
> Regards
>
> i4004

--

Dave Peterson

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

"i4004" wrote:

> 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!
>
> --
> Regards
>
> 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


"Dave Peterson" wrote:

> 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
>
> i4004 wrote:
> >
> > 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!
> >
> > --
> > Regards
> >
> > i4004
>
> --
>
> Dave Peterson
>

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.


--
Regards

i4004


"i4004" wrote:

> 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!
>
> --
> Regards
>
> 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?).

--
Regards

i4004


"i4004" wrote:

> 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!
>
> --
> Regards
>
> i4004

Only Office 2003 Professional supports XLM

What edition are you running?


Gord Dibben MS Excel MVP

On Sun, 22 Jun 2008 12:33:01 -0700, i4004 <(E-Mail Removed)>
wrote:

>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?).

I think you meant XML (not xlm).

I'm not sure what the OP really meant.

Gord Dibben wrote:
>
> Only Office 2003 Professional supports XLM
>
> What edition are you running?
>
> Gord Dibben MS Excel MVP
>
> On Sun, 22 Jun 2008 12:33:01 -0700, i4004 <(E-Mail Removed)>
> wrote:
>
> >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?).

--

Dave Peterson

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

Gord

On Mon, 23 Jun 2008 19:16:41 -0500, Dave Peterson <(E-Mail Removed)>
wrote:

>I think you meant XML (not xlm).
>
>I'm not sure what the OP really meant.
>
>Gord Dibben wrote:
>>
>> Only Office 2003 Professional supports XLM
>>
>> What edition are you running?
>>
>> Gord Dibben MS Excel MVP
>>
>> On Sun, 22 Jun 2008 12:33:01 -0700, i4004 <(E-Mail Removed)>
>> wrote:
>>
>> >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?).