Need to create an automatic hyperlink

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

 

[gloria.lewis]

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

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

 

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

 

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

 

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

 

[Gloria Lewis]

Thanks, had to tweak a little:

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

but this worked.