how to link to external worksheet

[veek]

I am using excel 2003 and am trying to link a worksheet from one spreadsheet
(source) into a second spreadsheet (destination). The source spreadsheet may
or may not be open when accessing the link from the destination spreadsheet.
I figured out to go to the source, select the whole sheet, ^c, then switch to
a blank worksheet in the destination and Edit->Paste->Paste Special. From
there I "Paste link", then paste "format" and "column widths".

My first problem is that any blank cells on the source worksheet come over
as 0's on the destination, even if I change cell type to text. I caught
something in Excel Help that said when linking, excel will put absolute
values in for any blanks which I assume is what I am seeing - but me no likey.

My second problem is that I don't want to actually link to the entire source
worksheet - I need to somehow be able to define the range as
A1:whatever-the-last-cell-in-row-F-with-content-is (and that will be
dynamic).

Finally, I am sure there is any easier way to do this than three separate
Paste Special commands (link, format, column widths), but alas I am a novice.
Any experts care to help me out? I have been scouring the web and excel
help and this board for hours and my brain hurts.

Oh, and I tried to create a function for the empty worksheet on the
destination with a formula like this:
=([SourceFileName.xls]WorksheetName!A1:F50) but it did not like that - and
besides, as I mentioned above I don't really want to have that static F50 in
there (I just chose that because I am fairly certain the source worksheet
will never be larger than that but even as a novice I know that is a bad
idea).

 

[veek]

oh, i also tried going to the destination worksheet, clicking Save, typing =
and then selecting A1:F50, Window->SourceFilename, and selecting A1:F50
there, then enter. but it is only picking up the very first cell, and none
of the column widths or formatting.

 

[Gord Dibben]

Paste Link operation will not error trap for blank cells.

In Sheet2 A1 enter =IF(Sheet1!A1="","",Sheet1!A1)

Drag and copy that to rows and columns you wish linked.

This will not bring over formatting and widths.


Gord Dibben MS Excel MVP

 

[veek]

Thank you for your response. However - I'm sorry Gord, I do not understand
what "Paste Link operation will not error trap for blank cells." means - does
that mean I am stuck with the zeros?

I tried the formula you suggest below but get an invalid error so I am sure
I am misunderstanding something. But bottom line, is it possible to do what
I am trying to do? More importantly, is it possible for a novice to set
something like this up?

Basically, there are a series of reports, each in a unique spreadsheet, that
I am trying to consolidate into a single master spreadsheet with distinct
tabs for each report. Ultimately when people select a specified tab on the
destination spreadsheet, I want them to dynamically see the source worksheet.
i.e. I do not want the report worksheets to have to exist in two places, and
I do not want the users to have write access to the master spreadsheet (so
they cannot just do their report updating directly to the master).

--

thanks so much!
veek

Paste Link operation will not error trap for blank cells.

In Sheet2 A1 enter =IF(Sheet1!A1="","",Sheet1!A1)

Drag and copy that to rows and columns you wish linked.

This will not bring over formatting and widths.


Gord Dibben MS Excel MVP

I am using excel 2003 and am trying to link a worksheet from one spreadsheet
(source) into a second spreadsheet (destination). The source spreadsheet may
or may not be open when accessing the link from the destination spreadsheet.
I figured out to go to the source, select the whole sheet, ^c, then switch to
a blank worksheet in the destination and Edit->Paste->Paste Special. From
there I "Paste link", then paste "format" and "column widths".

My first problem is that any blank cells on the source worksheet come over
as 0's on the destination, even if I change cell type to text. I caught
something in Excel Help that said when linking, excel will put absolute
values in for any blanks which I assume is what I am seeing - but me no likey.

My second problem is that I don't want to actually link to the entire source
worksheet - I need to somehow be able to define the range as
A1:whatever-the-last-cell-in-row-F-with-content-is (and that will be
dynamic).

Finally, I am sure there is any easier way to do this than three separate
Paste Special commands (link, format, column widths), but alas I am a novice.
Any experts care to help me out? I have been scouring the web and excel
help and this board for hours and my brain hurts.

Oh, and I tried to create a function for the empty worksheet on the
destination with a formula like this:
=([SourceFileName.xls]WorksheetName!A1:F50) but it did not like that - and
besides, as I mentioned above I don't really want to have that static F50 in
there (I just chose that because I am fairly certain the source worksheet
will never be larger than that but even as a novice I know that is a bad
idea).

 

[Gord Dibben]

You get stuck with zeros if the source cells are blank when you use the
Paste Link although you could go to Tools>Options>View and opt to not show
zero values.

That may be easiest for you, then you could just select a range from source
and paste links to destination.

My formula trapped for the blank cells in the source.

When you are linking to another book you have to alter the formula to
include the book name as well as the sheet name and cell reference.

=IF([book1.xls]Sheet1!A1="","",[book1.xls]Sheet1!A1)


Gord

 

[veek]

Gord,

Once again - many thanks. I think I figured out what it is I am asking to
do - if this makes sense - I want to actually link a *tab* on the destination
spreadsheet to a worksheet from the source. When I click on the tab on the
dest ss, I want to see the source worksheet (whether or not the source ss is
open). And I want it to be seamless - to appear as though the source ws
actually resides in the dest ss. I see that there are ways to link
individual cells, or even a range of cells - but I've yet to find the way to
link to the entire sheet (whose size will be dynamic).

I think the one thing that is clear is that if this is even doable, it is
most certainly too complex for a novice such as myself

Have a great day
Veek
--

thanks so much!
veek

You get stuck with zeros if the source cells are blank when you use the
Paste Link although you could go to Tools>Options>View and opt to not show
zero values.

That may be easiest for you, then you could just select a range from source
and paste links to destination.

My formula trapped for the blank cells in the source.

When you are linking to another book you have to alter the formula to
include the book name as well as the sheet name and cell reference.

=IF([book1.xls]Sheet1!A1="","",[book1.xls]Sheet1!A1)


Gord

Thank you for your response. However - I'm sorry Gord, I do not understand
what "Paste Link operation will not error trap for blank cells." means - does
that mean I am stuck with the zeros?

I tried the formula you suggest below but get an invalid error so I am sure
I am misunderstanding something. But bottom line, is it possible to do what
I am trying to do? More importantly, is it possible for a novice to set
something like this up?

Basically, there are a series of reports, each in a unique spreadsheet, that
I am trying to consolidate into a single master spreadsheet with distinct
tabs for each report. Ultimately when people select a specified tab on the
destination spreadsheet, I want them to dynamically see the source worksheet.
i.e. I do not want the report worksheets to have to exist in two places, and
I do not want the users to have write access to the master spreadsheet (so
they cannot just do their report updating directly to the master).