Very Easy Question!

Jayker

New Member
Joined
Mar 21, 2013
Messages
20
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

AusSteelMan

Board Regular
Joined
Sep 4, 2009
Messages
208
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

New Member
Joined
Mar 21, 2013
Messages
20
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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

Board Regular
Joined
Sep 4, 2009
Messages
208

ADVERTISEMENT

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

Board Regular
Joined
Sep 4, 2009
Messages
208
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,196
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top