Very Easy Question!

[Jayker]

Hey!
I'm sure one or all of you can answer this question very easily for me.
I have one sheet with a bunch of jobs I'm working on. And I want to set a hyperlink to the next sheet that has more info about each job.
I have it set up so the jobs go across the columns on the second sheet.
When I set up hyperlink on the first sheet to link to, say column D3 on the second sheet, when I add a new column on the second sheet in front of D3 (now becoming the new D3) I want my hyperlink to automatically stay with what job it was (would now be E3).

Any way to do this?

Thanks for your help!
Jay

 

[AusSteelMan]

If you are using the "Insert > Hyperlink" tool (as against a formula), then one way is to Name the destination cell, then when you set up the hyperlink, select the Name instead of a cell reference

Regards,
Darren

 

[Jayker]

If you are using the "Insert > Hyperlink" tool (as against a formula), then one way is to Name the destination cell, then when you set up the hyperlink, select the Name instead of a cell reference

Regards,
Darren

Are you suggesting there is a formula to link it instead? If naming every one of the new jobs could be avoided, that could sure save some time

 

[AlphaFrog]

Try something like this where "Job 3" is the text to display (can be another cell reference)...

=HYPERLINK("#" & CELL("Address",Sheet2!D3),"Job 3")

 

[AusSteelMan]

The way to use a formula generically is:

=HYPERLINK("[File name]CellRef",FriendlyName)

eg

=HYPERLINK("[C:\Documents and Settings\jayker\Desktop\jayker.xlsx]Sheet2!D3","More Details")

However, being hard coded the link address does not update when you add a column. That is why you need to make Sheet2!D3 a Named range eg Sht2D3.

Now it will read:
=HYPERLINK("[C:\Documents and Settings\jayker\Desktop\jayker.xlsx]Sht2D3","More Details")

Of course the Friendly Name can be sourced from another cell, say A3 on your main sheet. So now...

=HYPERLINK("[C:\Documents and Settings\jayker\Desktop\jayker.xlsx]Sht2D3",A3)

If there is a way to do what you want without a Name, I don't know how right now. Perhaps someone else does.

All the best,
Darren

 

[AusSteelMan]

Thanks AlphaFrog.

I hadn't thought of CELL

Cheers

 

[AusSteelMan]

I also learnt about the "#" to save puting the whole address for the current book (at least that is what I figured out it does, correct?)

Thanks very much