Hyperlink based on cell value in a different worksheet

[Guest]

Hi there,

I'm attempting to create a hyperlink in Sheet2 that will open a folder whose
name is stored in a cell in Sheet1. Here's what I've got so far:

=HYPERLINK("I:\2007GSR\"&B7,"Link to Folder")

B7 contains the folder name but this assumes the cell is on the same sheet
as the hyperlink. How do I get the formula to realize it's not?

Thanks.

Bart

 

[mdschuetz]

Bart,


How will accessing the cell in Sheet1 provide the hyperlink to a folder I assume is located somewhere else on the network?

 

[Guest]

Bart you are very close:

=HYPERLINK("E:\" & Sheet1!B7,"link to folder")

adapt to your logical drive & folder name.

 

[Guest]

Thanks, that did the trick, figured it had to be something like that.

Maybe I should be putting this under a new post, but just to make things a
little more complicated, is it possible to do the samething, but instead of
referencing a cell on a different sheet, refence the name of the sheet the
hyperlink is on? For example, the folder to link to and the worksheet would
both be named "GSR 1". New folders/worksheets will be created as new
projects come in.

Any ideas?

 

[Guest]

I've managed to come up with a work around but I'm not sure if there's a
better way.

Put the following formula (which I got off the discussion group, no idea why
it works, just know it does...) in Cell A3 and hide column A just to keep
things clean:

=RIGHT(CELL("FILENAME",F10),LEN(CELL("FILENAME",F10))-FIND("]",CELL("FILENAME",F10)))

I then change the Hyperlink formula to:

=HYPERLINK("I:\2007GSR\"&A3,"Link to Folder")

It's not pretty, but it works.