Need to create an automatic hyperlink


G

gloria.lewis

I'm using Excel 2003, Windows XP.

Okay I have a workbook with a main spreadsheet and about 150 other
spreadsheets with images in them.

I have the names of the other spreadsheets in cell b2.

How do I create a hyperlink that will concantenate the name in cell b2 to a
hyperlink and point it to the spreadsheet with that name? I know I can do it
by manually selecting each cell and hyperlinking manually, but with over 150
it's taking a while.

Thanks.
 
Ad

Advertisements

G

gloria.lewis

I tried
=HYPERLINK("file:////"&b2,"Click me!")

and it fails (can't find specified file).
 
D

Dave Peterson

Does B2 include the drive and path (or UNC path)?

Maybe something like:

=HYPERLINK("file:////c:\my folder\"&b2,"Click me!")
or
=HYPERLINK("file:////server\sharename\"&b2,"Click me!")

If this doesn't help, what's the name of the file?

======
I like to put the folder name (including the final backslash) in a cell (like
A1) and use:

=HYPERLINK("file:////"&$a$1&b2,"Click me!")

Then if the folder ever changes, I only have to change that one cell.
 
G

Gloria Lewis

Do I need to include the drive and path?

I'm going to be emailing this to someone. They will not be saving it to the
same drive and path that I have it on. Since I'm trying to link to a sheet
within the same workbook, I would think that I don't need to include the
drive and path.
 
D

Dave Peterson

If you're linking to a cell in the same workbook, you don't want to use the
"file:////" stuff.

David McRitchie posted this and it might help you:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

The thing that's very nice about David's syntax is that if you insert or delete
rows/columns in Sheet3, then the formula will point to the new location.

You could also use this syntax:
=HYPERLINK("#Sheet3!C5","Click Here")

Will always point at C5 of Sheet3--no matter if you insert/delete rows or
columns--or delete Sheet3!!!

So you'll want to choose the one that fits your requirements.
 
Ad

Advertisements

G

Gloria Lewis

Thanks, had to tweak a little:

=HYPERLINK("#" & "'" & B2 & "'!A1",B2)

but this worked.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top