View Full Version : Microsoft Excel: How to link multiple workbooks??

amyboonsiriseth

04-26-2006, 10:53 PM

I have a scenario where I wanted to have a summary excel sheet point to a fixed cell in other closed excel workbooks. When the end user creates a new line in excel I have a formula where I use concatenate to construct the path of the excel workbook relating to the physical path in the file system. To convert a formula into the cell reference I tried to use INDIRECT function, but this function only displays value when the excel workbook linked to is open. This summary sheet which has links to other excel sheet points to more than 250 excel workbooks and it is impractical to keep all the excel workbooks open to display the values in the summary sheet. I am trying to look at the code where I can try to simulate the same way of opening an excel sheet, copying the cell reference, do a paste special as a link which is holding the value, and close the sheet (repeat with other workbook). What I was planning to do is when the end user opens up the summary sheet I wanted to basically grab the paths of the individual excel sheets and show up in the summary excel sheet. If the individual file does not exist I wanted to put a string saying "Not Valid Path" in the summary sheet. Can you please help me out to look for code to achieve this objective. Your help is greatly apprecaited. Thanks!

Nomad

04-26-2006, 11:53 PM

I have a scenario where I wanted to have a summary excel sheet point to a fixed cell in other closed excel workbooks.

When the end user creates a new line in excel I have a formula where I use concatenate to construct the path of the excel workbook relating to the physical path in the file system. To convert a formula into the cell reference I tried to use INDIRECT function, but this function only displays value when the excel workbook linked to is open.

This summary sheet which has links to other excel sheet points to more than 250 excel workbooks and it is impractical to keep all the excel workbooks open to display the values in the summary sheet. I am trying to look at the code where I can try to simulate the same way of opening an excel sheet, copying the cell reference, do a paste special as a link which is holding the value, and close the sheet (repeat with other workbook).

What I was planning to do is when the end user opens up the summary sheet I wanted to basically grab the paths of the individual excel sheets and show up in the summary excel sheet. If the individual file does not exist I wanted to put a string saying "Not Valid Path" in the summary sheet. Can you please help me out to look for code to achieve this objective. Your help is greatly apprecaited. Thanks!

at least try to make it legible to understand please.... Thankyou :)

Maybe you might something over at www.hotscripts.com under programming or .net/.asp ?

Other than that haven't a clue.. sorry

:)

amyboonsiriseth

04-27-2006, 12:26 AM

I figure if people can discuss chairs, I can certainly try and find an Excel expert here!

J in Calgary

04-27-2006, 12:31 AM

I figure if people can discuss chairs, I can certainly try and find an Excel expert here!

:) I wish I could help you. I am not well-versed in Excel, and I don't even have a new chair, but I hope someone will be able to help. :)

Nomad

04-27-2006, 01:02 AM

I figure if people can discuss chairs, I can certainly try and find an Excel expert here!

Most deginately and nothing is stopping you but what would you rather read?

This?

I have a scenario where I wanted to have a summary excel sheet point to a fixed cell in other closed excel workbooks. When the end user creates a new line in excel I have a formula where I use concatenate to construct the path of the excel workbook relating to the physical path in the file system. To convert a formula into the cell reference I tried to use INDIRECT function, but this function only displays value when the excel workbook linked to is open. This summary sheet which has links to other excel sheet points to more than 250 excel workbooks and it is impractical to keep all the excel workbooks open to display the values in the summary sheet. I am trying to look at the code where I can try to simulate the same way of opening an excel sheet, copying the cell reference, do a paste special as a link which is holding the value, and close the sheet (repeat with other workbook). What I was planning to do is when the end user opens up the summary sheet I wanted to basically grab the paths of the individual excel sheets and show up in the summary excel sheet. If the individual file does not exist I wanted to put a string saying "Not Valid Path" in the summary sheet. Can you please help me out to look for code to achieve this objective. Your help is greatly apprecaited. Thanks!

or this

I have a scenario where I wanted to have a summary excel sheet point to a fixed cell in other closed excel workbooks.

When the end user creates a new line in excel I have a formula where I use concatenate to construct the path of the excel workbook relating to the physical path in the file system. To convert a formula into the cell reference I tried to use INDIRECT function, but this function only displays value when the excel workbook linked to is open.

This summary sheet which has links to other excel sheet points to more than 250 excel workbooks and it is impractical to keep all the excel workbooks open to display the values in the summary sheet. I am trying to look at the code where I can try to simulate the same way of opening an excel sheet, copying the cell reference, do a paste special as a link which is holding the value, and close the sheet (repeat with other workbook).

What I was planning to do is when the end user opens up the summary sheet I wanted to basically grab the paths of the individual excel sheets and show up in the summary excel sheet. If the individual file does not exist I wanted to put a string saying "Not Valid Path" in the summary sheet. Can you please help me out to look for code to achieve this objective. Your help is greatly apprecaited. Thanks!

Don't forget the people you are asking for help are also probably busy with there own work as well and drop by when they have a bit of spare time to happily help out.

Whilst we are happy to, at least make it easier for us to understand what you require, give as much detail as possible but above all make it legible to read and understand. It will helpm us to understand what you need and how if we cam help you more expediantly.

As I said check www.hotscripts.com for scripts relating to excell interaction - I'm sure you will find something there - I use it all the time for my php/mysql scripts and it hasn't let me down as yet - theres plenty of free stuff there as well...

Help us to help you is the key :)

webado

04-27-2006, 02:00 AM

I'd think you have to use vb scripting to handle Excel data.

I have a number of externally linked worksheets and I thought I was a reasonably competent user but you're on a different plain to the rest of us. ;) I think you're going to have to search out a specialist Excel forum or group.

If you search Google Groups for 'Excell external file linking' a number of possibilities come up including 'Microsoft.public.excel.programming' which is the sort of place you might find your solution.

amyboonsiriseth

04-27-2006, 06:46 PM

Thank for the advice. Obviously I'm not using this forum as my only source of help. I have searched google and in fact the initial question phrasing of the problem I "borrowed" from another confused Excel user on the net, since he had the exact same problem I have encountered with Excel. Since I have posted on this forum before, thought I'd just shoot it off to see if anyone by chance have done this before.

Still, if anyone out there have done this before, any advice or ideas would be appreciated! :idea:

dtshumaker

08-29-2007, 08:29 PM

Hi. I have a similar issue I'm trying to resolve. I want to create external references to specific cells in another workbook by stringing together the path, filename and cell reference. I can build that string correctly; it looks just like the formula generated when I create the reference maually. (I.e., I enter = in the destination cell, activate the source workbook and cell, then press Enter.) But Excel does not treat my string as a formula; it treats is simply as text. Like you, I tried using the string as the argument in the INDIRECT statement and I have altered the data category of the cell (Format Cells, Number) but to no effect.

It has been a while since your first post in this chain. Did you ever find an answer to your issue?

7even

10-11-2007, 03:23 PM

Hi all,

I encountered the same problem and had not found a solution until now. For this task I wanna avoid using a macro, because it should not be such a big thing!

But if s.o. says that this is not possible to realize with excel native formulas I will choose the macro's way of solving issues.. ;)

Brief summary of our problem:

Working manual formula:

='C:\abc\def\ghi\[jkl.xlsx]Sheet1'!$CELL$1

Not working dynamic formulas:

='C:\abc\def\ghi\[' & $A$1 & ']Sheet1'!$CELL$1

=$A$2!$CELL$1

where referenced cells contains e. g. the following:

- $A$1: jkl.xlsx

- $A$2: C:\abc\def\ghi\[jkl.xlsx]Sheet1

As mentioned above, I also tried INDIRECT-function, but no change.

There should be some kind of function that evaluates given parameters as path or something similar..

7even

geordie joe

10-11-2007, 07:33 PM

I seem to remember when I did this a couple of years ago I just opened the spreadsheets I wanted to link to an clicked on the cells.

Suppose you have a spreadsheet called Summary.xls and you want to link a cell in it (A1) to cell A1 in a book called Data.xls.

1. Open both spreadsheets

2. Click into cell A1 in Summary.xls

3. Type "=" then switch to the Data.xls spreadsheet and click into cell A1

4. Switch back to the Summary spreadsheet and click the Tick button to accept the formula.

The link will be made for you and you can then repeat the process for other cells or spreadsheets. Or just copy the formula into other cells changing the location of the linked cells/sheets.

beware, the Summary spradsheet will also store the information about the linked sheets and it looks for them each time you open it. If you delete, rename or change the location of the linked spreadsheets, in this case the Data.xls spreadsheet the summary spreadsheet will delete the links when you open it.

geordie joe

10-11-2007, 07:41 PM

OK, I've just tried it and I was right, that's the way to do it.

One thing, I tried it with two spreadsheets in different folders, but the formula it produced was

=[data_sheet.xls]Sheet1!$A$1

The information about the location of the spreadsheet data_sheet.xls is stored in the links section. Found under the edit menu.

awasp67

10-11-2007, 09:57 PM

Once the file is closed you need the full path to it (which excel will add automatically) so you need

=+'C:\Documents and Settings\bob\Mes documents\[Book1.xls]Sheet1'!$A$1

(excuse my french)

If the OP can explain why the file path needs to be dynamically produced I'll try and help.

7even

10-12-2007, 11:51 AM

Hi and thanks for your answers,

but the manual way of linking foreign cells worked for me as well (as I described).

The reason for creating this link dynamically is that I use not only one cell in each foreign excel sheet. Therefore I write the filename of the excel sheet in the first column and try to use this cell to create the link in the following columns.

Example schema:

xyz.xlsx | ='a:\bcd\efg\' & $A1 & 'Sheet1!$CELL$1 | ='a:\bcd\efg\' & $A1 & 'Sheet1!$CELL$5678

Where $A1 refers to the first column with content 'xyz.xlsx'.

vBulletin® v3.8.7, Copyright ©2000-2015, vBulletin Solutions, Inc.