#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Linking in Excel

Can anyone explain:
1. What are the advantages to linking in Excel as compared to using linked
cells with in a work book of several sheets?

2. Is there a simple method to insert a link between workbook worksheets?
C:Data\Costs\..................................... ...................
Ordoes it have to be entered manually?

3. Faced with several large workbooks with many formulae connected by links.
What is the best method to anaylis the spreadsheets?

Thanks in advance
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 119
Default Linking in Excel


"linking in Excel" ? what's this refer to?

- For inserting links, 2 quick ways:

1. you click the source, choose copy and go to destination work book, choose
paste special paste as link
2. you go to destination, then type "=" and use mouse to choose the source
workbook and click the destination directly.


- In order to manage links between workbooks, you could use Find, below is
from Excel help:


==
There is no automatic way to find links used in a workbook. However, because
links use brackets [ ] to enclose the source workbook (source file: The file
that contains information that was used to create a linked or embedded
object. When you update the information in the source file, you can also
update the linked object in the destination file.) name when it is closed,
you can search for it. For example =SUM([Budget.xls]Annual!C10:C25).

Close all workbooks except the one you want to find the links in.
On the Edit menu, click Find.
Click Options.
In the Find what box, enter [.
In the Within box, click Workbook.
In the Look In box, click Formulas.
Click Find All.
In the box at the bottom, look in the Formula column for formulas that
contain [.
To select the cell with a link, select the row in the box at the bottom.
Links are also commonly used in names, text boxes, or chart titles.
==


I think that will make your life easier to manage all this.

Also, there are pros and cons whether you put the source in workbook and or
put both of them in the same spreadsheet. Also you could use 'hide' the
spreadsheet and protect the workbook so as to hide the source as well in case
you want to make it easier to collaborate.

hope this help.





"Dermot" wrote:

Can anyone explain:
1. What are the advantages to linking in Excel as compared to using linked
cells with in a work book of several sheets?

2. Is there a simple method to insert a link between workbook worksheets?
C:Data\Costs\..................................... ...................
Ordoes it have to be entered manually?

3. Faced with several large workbooks with many formulae connected by links.
What is the best method to anaylis the spreadsheets?

Thanks in advance

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Linking in Excel

Thanks fot the reply Leung

I will try this out.

"Leung" wrote:


"linking in Excel" ? what's this refer to?

- For inserting links, 2 quick ways:

1. you click the source, choose copy and go to destination work book, choose
paste special paste as link
2. you go to destination, then type "=" and use mouse to choose the source
workbook and click the destination directly.


- In order to manage links between workbooks, you could use Find, below is
from Excel help:


==
There is no automatic way to find links used in a workbook. However, because
links use brackets [ ] to enclose the source workbook (source file: The file
that contains information that was used to create a linked or embedded
object. When you update the information in the source file, you can also
update the linked object in the destination file.) name when it is closed,
you can search for it. For example =SUM([Budget.xls]Annual!C10:C25).

Close all workbooks except the one you want to find the links in.
On the Edit menu, click Find.
Click Options.
In the Find what box, enter [.
In the Within box, click Workbook.
In the Look In box, click Formulas.
Click Find All.
In the box at the bottom, look in the Formula column for formulas that
contain [.
To select the cell with a link, select the row in the box at the bottom.
Links are also commonly used in names, text boxes, or chart titles.
==


I think that will make your life easier to manage all this.

Also, there are pros and cons whether you put the source in workbook and or
put both of them in the same spreadsheet. Also you could use 'hide' the
spreadsheet and protect the workbook so as to hide the source as well in case
you want to make it easier to collaborate.

hope this help.





"Dermot" wrote:

Can anyone explain:
1. What are the advantages to linking in Excel as compared to using linked
cells with in a work book of several sheets?

2. Is there a simple method to insert a link between workbook worksheets?
C:Data\Costs\..................................... ...................
Ordoes it have to be entered manually?

3. Faced with several large workbooks with many formulae connected by links.
What is the best method to anaylis the spreadsheets?

Thanks in advance

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 663
Default Linking in Excel

Hi,

There are two help topics I have copied to you here, Find links in a
workbook and HYPERLINKS.

1. Find links in a workbook

There is no automatic way to find links used in a workbook. However, because
links use brackets [ ] to enclose the source workbook (source file: The file
that contains information that was used to create a linked or embedded
object. When you update the information in the source file, you can also
update the linked object in the destination file.) name when it is closed,
you can search for it. For example =SUM([Budget.xls]Annual!C10:C25).

Close all workbooks except the one you want to find the links in.
On the Edit menu, click Find.
Click Options.
In the Find what box, enter [.
In the Within box, click Workbook.
In the Look In box, click Formulas.
Click Find All.
In the box at the bottom, look in the Formula column for formulas that
contain [.
To select the cell with a link, select the row in the box at the bottom.
Links are also commonly used in names, text boxes, or chart titles.

A. Find links used in names

On the Insert menu, point to Name, and then click Define.

Select each entry in the Names in workbook list, and look in the Refers to
box for links. Links contain a reference to another workbook, such as
[Budget.xls].

B. Find links used in text boxes or chart titles

Click the text box or chart title you want to check.
In the formula bar (formula bar: A bar at the top of the Excel window that
you use to enter or edit values or formulas in cells or charts. Displays the
constant value or formula stored in the active cell.) , look for a reference
to another workbook, such as [Budget.xls].

c. Find links used in other objects

On the Edit menu, click Go To, and then click Special.
Click Objects, and then click OK.
Press TAB, and then look in the formula bar (formula bar: A bar at the top
of the Excel window that you use to enter or edit values or formulas in cells
or charts. Displays the constant value or formula stored in the active cell.)
for a reference to another workbook, such as [Budget.xls]. The name of the
object appears in the Name box.
Repeat the previous step until each object is checked.

D. Find links used in chart data series

Select the data series you want to check.
In the formula bar (formula bar: A bar at the top of the Excel window that
you use to enter or edit values or formulas in cells or charts. Displays the
constant value or formula stored in the active cell.) , look for a reference
to another workbook, such as [Budget.xls].
To proceed to the next data series, press TAB.
Repeat the previous two steps until each data series is checked.

2. HYPERLINK

Creates a shortcut or jump that opens a document stored on a network server,
an intranet (intranet: A network within an organization that uses Internet
technologies (such as the HTTP or FTP protocol). By using hyperlinks, you can
explore objects, documents, pages, and other destinations on the intranet.),
or the Internet. When you click the cell that contains the HYPERLINK
function, Microsoft Excel opens the file stored at link_location.

Syntax

HYPERLINK(link_location,friendly_name)

Link_location is the path and file name to the document to be opened as
text. Link_location can refer to a place in a document€” such as a specific
cell or named range in an Excel worksheet or workbook, or to a bookmark in a
Microsoft Word document. The path can be to a file stored on a hard disk
drive, or the path can be a universal naming convention (UNC) path on a
server (in Microsoft Excel for Windows) or a Uniform Resource Locator (URL
(Uniform Resource Locator (URL): An address that specifies a protocol (such
as HTTP or FTP) and a location of an object, document, World Wide Web page,
or other destination on the Internet or an intranet, for example:
http://www.microsoft.com/.)) path on the Internet or an intranet.

Link_location can be a text string enclosed in quotation marks or a cell
that contains the link as a text string.

If the jump specified in link_location does not exist or cannot be
navigated, an error appears when you click the cell.

Friendly_name is the jump text or numeric value that is displayed in the
cell. Friendly_name is displayed in blue and is underlined. If friendly_name
is omitted, the cell displays the link_location as the jump text.

Friendly_name can be a value, a text string, a name, or a cell that contains
the jump text or value.

If friendly_name returns an error value (for example, #VALUE!), the cell
displays the error instead of the jump text.

Remark

To select a cell that has a hyperlink in it without jumping to the hyperlink
destination, click the cell and hold the mouse button until the cursor
becomes a cross , then release the mouse button.

Examples

The following example opens a worksheet named Budget Report.xls that is
stored on the Internet at the location named example.microsoft.com/report and
displays the text "Click for report":

=HYPERLINK("http://example.microsoft.com/report/budget report.xls", "Click
for report")

The following example creates a hyperlink to cell F10 on the worksheet named
Annual in the workbook Budget Report.xls, which is stored on the Internet at
the location named example.microsoft.com/report. The cell on the worksheet
that contains the hyperlink displays the contents of cell D1 as the jump text:

=HYPERLINK("[http://example.microsoft.com/report/budget
report.xls]Annual!F10", D1)

The following example creates a hyperlink to the range named DeptTotal on
the worksheet named First Quarter in the workbook Budget Report.xls, which is
stored on the Internet at the location named example.microsoft.com/report.
The cell on the worksheet that contains the hyperlink displays the text
"Click to see First Quarter Department Total":

=HYPERLINK("[http://example.microsoft.com/report/budget report.xls]First
Quarter!DeptTotal", "Click to see First Quarter Department Total")

To create a hyperlink to a specific location in a Microsoft Word document,
you must use a bookmark to define the location you want to jump to in the
document. The following example creates a hyperlink to the bookmark named
QrtlyProfits in the document named Annual Report.doc located at
example.microsoft.com:

=HYPERLINK("[http://example.microsoft.com/Annual Report.doc]QrtlyProfits",
"Quarterly Profit Report")

In Excel for Windows, the following example displays the contents of cell D5
as the jump text in the cell and opens the file named 1stqtr.xls, which is
stored on the server named FINANCE in the Statements share. This example uses
a UNC path:

=HYPERLINK("\\FINANCE\Statements\1stqtr.xls", D5)

The following example opens the file 1stqtr.xls in Excel for Windows that is
stored in a directory named Finance on drive D, and displays the numeric
value stored in cell H10:

=HYPERLINK("D:\FINANCE\1stqtr.xls", H10)

In Excel for Windows, the following example creates a hyperlink to the area
named Totals in another (external) workbook, Mybook.xls:

=HYPERLINK("[C:\My Documents\Mybook.xls]Totals")

In Microsoft Excel for the Macintosh, the following example displays "Click
here" in the cell and opens the file named First Quarter that is stored in a
folder named Budget Reports on the hard drive named Macintosh HD:

=HYPERLINK("Macintosh HD:Budget Reports:First Quarter", "Click here")

You can create hyperlinks within a worksheet to jump from one cell to
another cell. For example, if the active worksheet is the sheet named June in
the workbook named Budget, the following formula creates a hyperlink to cell
E56. The link text itself is the value in cell E56.

=HYPERLINK("[Budget]June!E56", E56)

To jump to a different sheet in the same workbook, change the name of the
sheet in the link. In the previous example, to create a link to cell E56 on
the September sheet, change the word "June" to "September."


Challa Prabhu


"Dermot" wrote:

Can anyone explain:
1. What are the advantages to linking in Excel as compared to using linked
cells with in a work book of several sheets?

2. Is there a simple method to insert a link between workbook worksheets?
C:Data\Costs\..................................... ...................
Ordoes it have to be entered manually?

3. Faced with several large workbooks with many formulae connected by links.
What is the best method to anaylis the spreadsheets?

Thanks in advance

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 137
Default Linking in Excel

Thanks for the info challa prabhu.
Much appreciated



"challa prabhu" wrote:

Hi,

There are two help topics I have copied to you here, Find links in a
workbook and HYPERLINKS.

1. Find links in a workbook

There is no automatic way to find links used in a workbook. However, because
links use brackets [ ] to enclose the source workbook (source file: The file
that contains information that was used to create a linked or embedded
object. When you update the information in the source file, you can also
update the linked object in the destination file.) name when it is closed,
you can search for it. For example =SUM([Budget.xls]Annual!C10:C25).

Close all workbooks except the one you want to find the links in.
On the Edit menu, click Find.
Click Options.
In the Find what box, enter [.
In the Within box, click Workbook.
In the Look In box, click Formulas.
Click Find All.
In the box at the bottom, look in the Formula column for formulas that
contain [.
To select the cell with a link, select the row in the box at the bottom.
Links are also commonly used in names, text boxes, or chart titles.

A. Find links used in names

On the Insert menu, point to Name, and then click Define.

Select each entry in the Names in workbook list, and look in the Refers to
box for links. Links contain a reference to another workbook, such as
[Budget.xls].

B. Find links used in text boxes or chart titles

Click the text box or chart title you want to check.
In the formula bar (formula bar: A bar at the top of the Excel window that
you use to enter or edit values or formulas in cells or charts. Displays the
constant value or formula stored in the active cell.) , look for a reference
to another workbook, such as [Budget.xls].

c. Find links used in other objects

On the Edit menu, click Go To, and then click Special.
Click Objects, and then click OK.
Press TAB, and then look in the formula bar (formula bar: A bar at the top
of the Excel window that you use to enter or edit values or formulas in cells
or charts. Displays the constant value or formula stored in the active cell.)
for a reference to another workbook, such as [Budget.xls]. The name of the
object appears in the Name box.
Repeat the previous step until each object is checked.

D. Find links used in chart data series

Select the data series you want to check.
In the formula bar (formula bar: A bar at the top of the Excel window that
you use to enter or edit values or formulas in cells or charts. Displays the
constant value or formula stored in the active cell.) , look for a reference
to another workbook, such as [Budget.xls].
To proceed to the next data series, press TAB.
Repeat the previous two steps until each data series is checked.

2. HYPERLINK

Creates a shortcut or jump that opens a document stored on a network server,
an intranet (intranet: A network within an organization that uses Internet
technologies (such as the HTTP or FTP protocol). By using hyperlinks, you can
explore objects, documents, pages, and other destinations on the intranet.),
or the Internet. When you click the cell that contains the HYPERLINK
function, Microsoft Excel opens the file stored at link_location.

Syntax

HYPERLINK(link_location,friendly_name)

Link_location is the path and file name to the document to be opened as
text. Link_location can refer to a place in a document€” such as a specific
cell or named range in an Excel worksheet or workbook, or to a bookmark in a
Microsoft Word document. The path can be to a file stored on a hard disk
drive, or the path can be a universal naming convention (UNC) path on a
server (in Microsoft Excel for Windows) or a Uniform Resource Locator (URL
(Uniform Resource Locator (URL): An address that specifies a protocol (such
as HTTP or FTP) and a location of an object, document, World Wide Web page,
or other destination on the Internet or an intranet, for example:
http://www.microsoft.com/.)) path on the Internet or an intranet.

Link_location can be a text string enclosed in quotation marks or a cell
that contains the link as a text string.

If the jump specified in link_location does not exist or cannot be
navigated, an error appears when you click the cell.

Friendly_name is the jump text or numeric value that is displayed in the
cell. Friendly_name is displayed in blue and is underlined. If friendly_name
is omitted, the cell displays the link_location as the jump text.

Friendly_name can be a value, a text string, a name, or a cell that contains
the jump text or value.

If friendly_name returns an error value (for example, #VALUE!), the cell
displays the error instead of the jump text.

Remark

To select a cell that has a hyperlink in it without jumping to the hyperlink
destination, click the cell and hold the mouse button until the cursor
becomes a cross , then release the mouse button.

Examples

The following example opens a worksheet named Budget Report.xls that is
stored on the Internet at the location named example.microsoft.com/report and
displays the text "Click for report":

=HYPERLINK("http://example.microsoft.com/report/budget report.xls", "Click
for report")

The following example creates a hyperlink to cell F10 on the worksheet named
Annual in the workbook Budget Report.xls, which is stored on the Internet at
the location named example.microsoft.com/report. The cell on the worksheet
that contains the hyperlink displays the contents of cell D1 as the jump text:

=HYPERLINK("[http://example.microsoft.com/report/budget
report.xls]Annual!F10", D1)

The following example creates a hyperlink to the range named DeptTotal on
the worksheet named First Quarter in the workbook Budget Report.xls, which is
stored on the Internet at the location named example.microsoft.com/report.
The cell on the worksheet that contains the hyperlink displays the text
"Click to see First Quarter Department Total":

=HYPERLINK("[http://example.microsoft.com/report/budget report.xls]First
Quarter!DeptTotal", "Click to see First Quarter Department Total")

To create a hyperlink to a specific location in a Microsoft Word document,
you must use a bookmark to define the location you want to jump to in the
document. The following example creates a hyperlink to the bookmark named
QrtlyProfits in the document named Annual Report.doc located at
example.microsoft.com:

=HYPERLINK("[http://example.microsoft.com/Annual Report.doc]QrtlyProfits",
"Quarterly Profit Report")

In Excel for Windows, the following example displays the contents of cell D5
as the jump text in the cell and opens the file named 1stqtr.xls, which is
stored on the server named FINANCE in the Statements share. This example uses
a UNC path:

=HYPERLINK("\\FINANCE\Statements\1stqtr.xls", D5)

The following example opens the file 1stqtr.xls in Excel for Windows that is
stored in a directory named Finance on drive D, and displays the numeric
value stored in cell H10:

=HYPERLINK("D:\FINANCE\1stqtr.xls", H10)

In Excel for Windows, the following example creates a hyperlink to the area
named Totals in another (external) workbook, Mybook.xls:

=HYPERLINK("[C:\My Documents\Mybook.xls]Totals")

In Microsoft Excel for the Macintosh, the following example displays "Click
here" in the cell and opens the file named First Quarter that is stored in a
folder named Budget Reports on the hard drive named Macintosh HD:

=HYPERLINK("Macintosh HD:Budget Reports:First Quarter", "Click here")

You can create hyperlinks within a worksheet to jump from one cell to
another cell. For example, if the active worksheet is the sheet named June in
the workbook named Budget, the following formula creates a hyperlink to cell
E56. The link text itself is the value in cell E56.

=HYPERLINK("[Budget]June!E56", E56)

To jump to a different sheet in the same workbook, change the name of the
sheet in the link. In the previous example, to create a link to cell E56 on
the September sheet, change the word "June" to "September."


Challa Prabhu


"Dermot" wrote:

Can anyone explain:
1. What are the advantages to linking in Excel as compared to using linked
cells with in a work book of several sheets?

2. Is there a simple method to insert a link between workbook worksheets?
C:Data\Costs\..................................... ...................
Ordoes it have to be entered manually?

3. Faced with several large workbooks with many formulae connected by links.
What is the best method to anaylis the spreadsheets?

Thanks in advance

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
linking in excel pooja Excel Discussion (Misc queries) 0 September 7th 06 03:29 PM
Excel Linking LaNeise Excel Discussion (Misc queries) 7 July 6th 06 05:07 PM
data entry in excel by linking to other word doc. or excel sheets wangxuqin Excel Worksheet Functions 1 April 14th 06 12:33 PM
Excel - Linking to other WBs Mike Faulkner Excel Worksheet Functions 0 October 5th 05 09:21 AM
excel not linking shaya Excel Worksheet Functions 5 May 26th 05 01:57 PM


All times are GMT +1. The time now is 11:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"