Hyperlink Excel

[robcarr]

HI,

I have this value in J8

='test sheet'!$F$9

In I8 i want to create a hyperlink to the location in J8

so when the user clicks I8 it goes to test sheet cell F9.

I cant get the hyperlink formula to do this

I have so far

=HYPERLINK("#J8","Forecast Accuracy")

i have to set this up for about 80 or so hyperlinks and I dont want to create them manually, so I was hoping to get the hyperlink formula to work, when i click on it at the moment it goes to J8 and not the sheet I want it to go to.


Hope this is of use, Rob.

 

[SkipVought]

I used a Named Range name NamedRange01 on 'test sheet' for $F$9.

In J8...
[tt]
J8: NamedRange01
[/tt]
Then my hyperlink formula...
[tt]
I9: =HYPERLINK("#"&J8,"Forecast Accuracy")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

It sounds like you want to access the formula in another cell, and then use that for the hyperlink. To be able to do this with formulas is a bit tricky. You'll need to set up a defined name Excel4Macro macro formula to get the formula of the required cell first, and then use that name in your hyperlink.

First, click in cell I8, and use menu command Insert/Name/Define ( Excel 2003 ) to create a defined name of GetFormulaCellToRight, with a definition of:
=GET.CELL(6,J8)

Now, in cell I8 type:
=HYPERLINK("#"&MID(GetFormulaCellToRight,2,999),"Forecast Accuracy")



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

Of course, Skip's method is easiest if you don't want to use the actual formula of a cell as the source. I think I was being a bit too complicated there.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[robcarr]

hi,

I dont want to go down the named range route, as this would mean me setting up 80 named ranges, I was hoping to just use the info in j8 to give me the link reference, as this would save me a lot of time.


Hope this is of use, Rob.

 

[GlennUK]

In that case my method is perfect for you.

Cheers, Glenn.

Beauty is in the eye of the beerholder.