how to link to external worksheet

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

-- 

thanks so much!
veek
0
veek (11)
7/24/2008 9:05:01 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
587 Views

Similar Articles

[PageSpeed] 26

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

thanks so much!
veek


"veek" wrote:

> 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).  
> 
> -- 
> 
> thanks so much!
> veek
0
veek (11)
7/24/2008 9:28:00 PM
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


On Thu, 24 Jul 2008 14:05:01 -0700, veek <veek@discussions.microsoft.com>
wrote:

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

0
Gord
7/24/2008 11:31:05 PM
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


"Gord Dibben" wrote:

> 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
> 
> 
> On Thu, 24 Jul 2008 14:05:01 -0700, veek <veek@discussions.microsoft.com>
> wrote:
> 
> >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).  
> 
> 
0
veek (11)
7/25/2008 3:18:05 PM
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


On Fri, 25 Jul 2008 08:18:05 -0700, veek <veek@discussions.microsoft.com>
wrote:

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

0
Gord
7/25/2008 10:46:35 PM
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


"Gord Dibben" wrote:

> 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
> 
> 
> On Fri, 25 Jul 2008 08:18:05 -0700, veek <veek@discussions.microsoft.com>
> wrote:
> 
> >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).
> 
> 
0
veek (11)
7/28/2008 2:25:01 PM
Reply:

Similar Artilces:

Moving Between Worksheets #2
I have a series of interrelated worksheets in a single spreadsheet program. How can I move between worksheets so that I always arrive at the same cell reference on the worksheet that move to, as on the one I am moving from? Group them just before the move. When you're ready to change sheets, hold <Ctrl> and click on the tab of the sheet you're going to work on next. Release <Ctrl> and click in that sheet tab again and you'll be in that new sheet with the focus on the cell you were in on the first sheet. You now have to "break" the grouping (ungroup) before...

Report Purchase Orders Linked to Sales Orders
Does anyone have a handy dandy report that shows Purchase Orders that are linked to Sales Orders with part number, qty's and due dates? -- Doug T If you have SmartList Builder, I might have something for you. -- Charles Allen, MVP "Doug T" wrote: > Does anyone have a handy dandy report that shows Purchase Orders that are > linked to Sales Orders with part number, qty's and due dates? > -- > Doug T Charles, Thanks for the reply. So there really isn't anything in GP that reports it? Such a handy feature with no decent reporting. Not unusual just t...

XML Export and worksheets
I am able to export my spreadsheet to an XML file, but what I'd really like to do is either: 1) Export only selected data to XML from within a worksheet OR 2) Export all the data within a single worksheet to XML. Right now, it seems I can only export ALL the data within my workbook.... Anyone have a solution? ...

Add a row to the bottom of a worksheet.
I have a macro that summaries data on sheet1, I then want it to append this data in the form of a new row to the bottom of sheet2. Is there a formula that I can use to locate the next blank row, or what is the best way to achieve this please. Maybe you could just copy and paste that row in your macro. Option Explicit sub testme() dim DestCell as range dim RngToCopy as range 'your code to get the summaries 'whatever row holds the set rngtocopy = worksheets("sheet1").range("a999").entirerow with worksheets("sheet2") set destcell = .c...

Link 'Excel Object in Word' to Excel
(Also posted in the Excel Discussion Group) I have a Word document which contains several embedded Excel objects which is sent to 600 clients. The clients complete the data collection in the word document and send back. We have a Excel worksheet for each client that uses the data in the embedded Excel objects. Is there a way to "link" or transfer the data in the 'Excel Object in Word' to the appropriate cells in the Excel worksheet? Because each Word document is customized for each client using Mail Merge and an Access database, and because there is much ve...

Worksheet Calc Gets Stopped When Sheet is Clicked
The worksheet I'm working with has thousands of formulas, so if the calculation stops, I need to restart the entire process. If I even attempt to access a menu item or drag the window around on the screen, even minimize the window, all calcs stop. Is there anyway to protect the spreadsheet from being interrupted so easily? I don't think so. In fact, I think many depend on this feature. Instead of waiting for the calculation process to finish, the user can just go to the next cell and start inputting more data. You may want to turn calculation to manual so xl doesn't even try...

Outlook Anywhere not connecting; Outlook 2007 client external to Exchange 2007 SP2
Running Exchange 2007 SP2 with a few users migrated from Novell Groupwise. The following works: Outlook 2003/2007 client (internal ONLY), ActiveSync on smartphones, Outlook Web Access w/ SSL (internal & external). I'm trying to get an Outlook 2007 client that is external to the network connected but for some reason things just won't work. I've enabled Outlook Anywhere on the Exchange server, but things just aren't working. Help!!! Bill Hi Bill, Do you have Port 80 & 443 open on your firewall? Did you enable NTLM or Basic Authentication for Ou...

double worksheets?
A co-worker was working in a regularly-used workbook. Suddenly a second, seemingly identical workbook opened; the two were named *.xls:1 and *.xls:2 respectively. A week ago, a similar situation happened with the same workbook, except the duplication occured upon opening, rather than after it was already open. At that time, when we closed one of the "versions" the colon-number additional extention disappeared. But after closing the remaining document and reopening, the duplication appeared again upon opening. Can anyone tell us what this means? Did she accidentally hit a comman...

Joining text with a linked date
Does anybody know how to change the decimal places after using &"*"? Even if the cell is set to 2 decimal places it will show the full amount of decimals in the sum then the *. Might be something simple but if so I cant see the wood for the tress. -- Mustard Head ------------------------------------------------------------------------ Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16309 View this thread: http://www.excelforum.com/showthread.php?threadid=276994 Use =TEXT(B2,"0.00")&"*" "Mustard Head&q...

Linking set of worksheets between different workbooks!
Hi, We have a workbook A- with 50 worksheets named as Week1 Week2 etc. We have to create a new workbook B- with same number of worksheets, grabbing values of 3 cells from corresponding A's worksheets. B's Week1 will pull values from 3 cells in A's Week1 B'1 Week2 will pull values from same 3 cells in A's Week2 and so on.. HOw can this be accomplished without manually changing the SHeet name on the formulas. Thanks Priya ...

Inserting photos as a link
Hi, I am new to Excel and trying to insert photos as a link in the spreadsheet - so when I select it, they pop up in a new window. I've used the hyperlink insert, but the link keeps referring to a local image address - as in localuser/host/sam/etc. So, I can't share the document with other workers on different computers. I am using a Mac - please help, we are working on Katrina relief efforts and have hit this minor technical wall. Thanks! You might want to condsider inserting the Images in to Comment Boxes of cells which contain the names of the images...... which will pop-up ...

Excel Links #4
I have an Excel WorkBook called EOH.xls that asks if I want to Update the Links upon its opening. I am opening this WorkBook through a VB application that I wrote but cannot figure out how to open the WorkBook while automatcially choosing 'NO' to Update the Links message. I can write code in another Excel WorkBook to open the EOH.xls WorkBook or by turning off the 'Ask to update links', but I would like to programmatically do so in my existing VB application. Is this possible and if so, how? Any suggestions? see one guess to your first post. Riggs wrote: > &...

FRx External Worksheet
I am trying to create a 4year financial sales report. 2004-2006 data is in the GL and I have to pull 2003 data from a spreadsheet. I created an external worksheet link in the row format and specified the row format in the tree as well as the name of the workbook in brackets with the worksheet name as well. The 2003 data is not printing on my report. In the row format, for Sales I specified B=D10 where B is the Sales column in the column layout and set as "WKS" and D10 is the cell reference for the Sales data in the excel file. I also tried @WKS(B=D10)and it doesn't work ei...

Sorting a portion of a worksheet
*Using Office 2007* I am using the sort function to organize a list by date and anniversary year. That works well. Then I insert blank lines (for readability) between groups of dates according to weeks on the calendar. No problem. Next, I want to sort the names alphabetically within the anniversary year and week. Each time it asks if I want to expand the selection. I say yes so that all of the data (name, anniv. year, anniv. date, job title, job location) stays together for each entry. Sometimes this works. Sometimes it doesn't. When it works, just the names of assoc...

Create a list and use drop down in other worksheets..possible?
Hi, I've search on how to do this task and found things similar but nothing exact. What I would like to do is create a 'list' of entries in one worksheet and then in another using a dropdown box select any entry from the 'list'. For example. Worksheet 1 A1 A2 A3 A4 ....etc Orange 100 0.5 17 Apple 88 7 23 Worksheet 2 I would like to be able choose from a drop down (combo box?) Orange and have the other cells (A2,A3,A4) follow suit in their respectable columns. My goal is to input all the food data in one worksheet and be able to ...

Linking Position Codes to Payroll Codes
I have read in the help how to link position codes to payroll codes. I am wondering why I would do this? What is the value in linking a position code to a payroll code? Does it have any affect on payroll. The idea is that a position has certain pay codes that can be used and you want to avoid having users pay executive bonuses to hourly warehouse clerks, as an example. -- Charles Allen, MVP "Debra" wrote: > I have read in the help how to link position codes to payroll codes. I am > wondering why I would do this? What is the value in linking a position code &...

Worksheets automatic references
I need a way to automatize the change of a worksheet name in a formule, for exemple: =Sheet1!A2*1,2% (formule 1) This formula is done manualy, but, if I itroduce a new sheet in my file, I'd like to put a name in a celule and get reference to the new sheet, for example: The name "Sheet10" is taped in the celule A1, the celule for reference. In the celule B3 I have the formule 1, and I want that the formule is changed to "=Sheet10!A2*1,2%" automaticaly. Is it possible? Can I do it without a macro? Thanks, CHK =INDIRECT("'"&A1&"'...

Linking & Emailing Multiple Work books
Hi there, Last time I was on here, you guys worked miracles....hoping for the same again ;-) Problem: I am not able to successfully send 4 workbooks by email to my boss and have the links remain active between the 4 documents that have been emailed. (They still shows link paths back to the original document location on my computer's hard drive) There is one Summary workbook linked to Workbook A, Workbook B and Workbook C. I want to be able to email the 4 documents to him, and have him be able to open up the Summary sheet, (by going to Edit > Links > Change Source, and choose the W...

Missing Customizatoins Link in Settings
When logged in as Admin I select SETTINGS there is no link for CUSTOMIZATIONS? I have not made any changes, could my permissions have dropped? If so how do I restore them quickly? Weird, I cleared the IE cache 2 times, the first time it showed me SETTINGS and CUSTOMIZATION but clicking them did nothing. The second time it worked? Are u talking about crm 4.0? There I had the same problem when I changed the language. changed back to english and the link was there again. ...

Linking only the format of a cell and not the data
Hi, I want to be able to automatically update the cells a13.e13 with the colours that are put into a1.e1, but not to copy the content of cells a1.e1. Appreciate anyone help on this since most of the posting have a solution for content and format simultaneously. I want to link only the format and not the content. Thanks in advance. Sandip. Hi unfortunately you can't link formats. Even using an event procedure like worksheet_change won't help as this event is not triggered by format changes >-----Original Message----- >Hi, > >I want to be able to automatically update ...

Report Page Header and detail fields link to adjust automatically in Access 2007
Report Page Header and detail fields link to adjust automatically in Access 2007. I DO NOT WANT my title to adjust with my details field. I want the title one size and the detail a totally different size. How do I sever the link between the two when I adjust one. For example I choose the detail textbox field and start expanding it, the title field above will expand with it. They are linked somehow and I want to remove it. I know how to seperate in Access 2003, but this won't work in 2007. I created the report using the wizard, and I know they are not grouped together because ...

Code: Paste Graph with no Edit-->links
Hi, Workbook 1, Sheet1 contains a graph (say over cells A10 to h20 for example) along with source data beside it (say in cells j10 to m15 as example). The following code is in a button: Cells.select selection.copy Workbooks.Add activesheet.paste The problem is that since it is .paste the graph contains links to the original workbook/sheet. But in fact i want the graph & the data to exist here without links to the original workbook/sheet. What is the code i should change in the button ? Note that i tried PasteSpecial with Paste:=xlvalues but the graphs go for a toss. Any hel...

Can't open highlighted link contained within an email message
When I receive an email and there is a link highlighted within the email, I can't click on the link and have it open. When I click on the link, nothing happens. Any ideas? ...

Link dll
Hi, I have two DLLs, A and B. A uses B's lib and B uses A's lib. A.lib is entered to "Object/library modules of "Project setting\Link" of B's workspace. B.lib is entered to "Object/library modules" of "Project setting\Link" of A's workspace. How to build A.DLL and B.DLL in this case ? Thanks Minh Essentially what you are saying is that you have link time circular dependency. Thats a no-no AFAIK. The way I have seen it done successfully is to remove the link time dependecy and then use COM to instantiate other dll. I guess you can also ...

ATlWinModuleInit link error
I am trying to use Visual Studio .NET 2003 complier to compile my existing application. But i am getting couple of link errors... error LNK2001: unresolved external symbol "long __stdcall ATL::AtlWinModuleInit(struct ATL::_ATL_WIN_MODULE70 *)" (? AtlWinModuleInit@ATL@@YGJPAU_ATL_WIN_ MODULE70@1@@Z) There was no problem when i used Visual Studio 6. Please help. ...