Software

Ask something
Question Locked

Linking Excel 2003 Worksheets

0 helpful
4 replies

by rjmoore

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have links between MS-Excel (2003, SP3) spreadsheets. So, I have an active spreadsheet that links to other spreadsheets serving purely as data sources. Some of these other spreadsheets can be closed, and, others need to be opened, before the links work - viz, before the #ref errors disappear in my active worksheet.

What determines whether a referenced "outside" spreadsheet needs to be open or not? I would like the links in my active spreadsheet all to resolve properly while the outside spreadsheets being linked back to remain closed. Can any one help? Many thanks.

4 replies

by seanferd

Formulas with links to other workbooks are displayed in two ways, depending on whether the source workbook? the one workbook that supplies data to a formula? is open or closed.

When the source is open, the link includes the workbook name in square brackets, followed by the worksheet name, an exclamation point (!), and the cells that the formula depends on. For example, the following formula adds the cells C10:C25 from the workbook named Budget.xls.

Link
=SUM([Budget.xls]Annual!C10:C25)

When the source is not open, the link includes the entire path.

Link
=SUM('C:\Reports\[Budget.xls]Annual'!C10:C25)

Note If the name of the other worksheet or workbook contains nonalphabetic characters, you must enclose the name (or the path) within single quotation marks.

Formulas that link to a defined name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) in another workbook use the workbook name followed by an exclamation point (!), and the name. For example, the following formula adds the cells in the range named Sales from the workbook named Budget.xls.

Link
=SUM(Budget!Sales)

Excel help > Working with data > Formulas > creating links > About linking to another workbook or program ( > What a link to another workbook looks like)

by rjmoore

Very deedy and useful reply. Much obliged! I owe you a bottle of decent wine when we meet, for this problem has annoyed me for a long time.

Thx.

by seanferd

Glad I could help - and equally glad I could find the right reference in the sometimes inscrutable MS help files. I didn't just want to say, "Use the full path". Glad I didn't, as the single quotes are needed.

Cheers!

This discussion is closed to new replies.