creating a hyperlink in an excel "cell" by adding the product of two c 3

[itsfisko]

Hi all.
I have a need to create hyperlinks to folders from excel
I have a list of the folders on the server (over 500) and a generic part of the path, can I some how add the two into a cell that I can then click to link to these folders?
Cheers crew

Some lead, some follow....I just Hope!

 

[Bowers74]

Use the HYPERLINK() function in Excel:

This comes straight out of the Excel Help File:

HYPERLINK

Creates a shortcut or jump that opens a document stored on a network server, an 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) 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&quot

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&quot

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&quot

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&quot

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&quot

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


I hope this helps!





Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[itsfisko]

Thanks for your amazingly quick response I am going through your reply,
My problem is that I have a list in one column and want to add this \\server2k1/pupils/ to the front to produce a link like this \\server2k1/pupils/02001 in another cell that I can then click to take me to the hyperlink target.
I have looked at =HYPERLINK but joining two cells to produce a link? John in damp UK

Some lead, some follow....I just Hope!

 

[bluedragon2]

data in cells A1 and B1:

=HYPERLINK(A1&B1)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[dcompto]

In testing, I placed in
G1, the path: c:\my documents B2, filename1: canvashelp_Guide_240.htm
B3, filename2: canvashelp_Guide_240.htm
B4, filename3: canvashelp_Guide_240.htm
then I placed in
C2, formula: =HYPERLINK(CONCATENATE($G$1,B2))
C3, formula: =HYPERLINK(CONCATENATE($G$1,B3))
C4, formula: =HYPERLINK(CONCATENATE($G$1,B4))
of course for C3 and C4, I just copied the formula from C2.

As far as using "server folders", I probably don't know what I'm talking about, but... can you use \\[computer name]\[path]?

 

[ETID]

yeah use "file:\\[computer name]\[path]\file.ext

 

[itsfisko]

You guys are the best Bowers74 started the ball rolling and BlueDragon2 finished it off stars all round.. Its brightened my Night. Cheers

Some lead, some follow....I just Hope!

 

[itsfisko]

Spoke too soon, although the link displayed is correct after adding the two cells it will not hyperlink.
It brings up a dialouge box in excel asking if I wish to open the file. if i say OK it fails to work. Any Ideas?
:-(

Some lead, some follow....I just Hope!

 

[itsfisko]

ETID .....well done you've cracked it it was the file:// bit I was missing John Stressed school tech

Some lead, some follow....I just Hope!