Cell referencing a span small prob.

[richh]

I was wondering if there was a way for a cell that references a span could spawn at the first cell referenced rather than the last.

example - cell A1 on sheet 1 references cells B1:B52 on sheet 2. When I click on A1, which is hyperlink, it takes me to B52 on sheet 2 rather than B1. Is there a way to make it spawn at B1?

 

[T. Valko]

I was wondering if there was a way for a cell that references a span could spawn at the first cell referenced rather than the last.

example - cell A1 on sheet 1 references cells B1:B52 on sheet 2. When I click on A1, which is hyperlink, it takes me to B52 on sheet 2 rather than B1. Is there a way to make it spawn at B1?

How is cell A1 referencing Sheet2!B1:B52?

Try this hyperlink formula entered in cell A1:

=HYPERLINK("#Sheet2!B1","B1")

Change "B1" with whatever you want to be displayed in the cell.

 

[richh]

A1 has the county name of Alpine in it. I just did cntl+k and entered in the hyper link range there.

how would i put that in while still retaining the word Alpine?

 

[T. Valko]

A1 has the county name of Alpine in it. I just did cntl+k and entered in the hyper link range there.

how would i put that in while still retaining the word Alpine?

Like this...

=HYPERLINK("#Sheet2!B1","Alpine")

 

[richh]

lol like you said in your first reply, which i read at least 3 times and just couldnt get it through my thick skull, eh?

but that doesnt seem to work for me. ive changed the 2nd sheet's name to "extended info". I've tried referencing it to =HYPERLINK("#extended_info!b3","Alpine"), but it says the reference is invalid. But what I'm looking to do is reference b3:b52 and upon click, will direct the click to b3 rather than all the way down to b52.

 

[p45cal]

Try in Sheet1's code module:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Set xxx = Evaluate(Target.SubAddress)
xxx.Cells(xxx.Cells.Count).Activate
End Sub

At the moment it will treat all hyperlinks the same, that is making the last cell in each hyperlink the active cell.

 

[T. Valko]

lol like you said in your first reply, which i read at least 3 times and just couldnt get it through my thick skull, eh?

but that doesnt seem to work for me. ive changed the 2nd sheet's name to "extended info". I've tried referencing it to =HYPERLINK("#extended_info!b3","Alpine"), but it says the reference is invalid. But what I'm looking to do is reference b3:b52 and upon click, will direct the click to b3 rather than all the way down to b52.

That works just fine for me in both Excel 2002 and 2007.

Did you delete the original non-formula hyperlink you were using?

 

[richh]

Try in Sheet1's code module:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Set xxx = Evaluate(Target.SubAddress)
xxx.Cells(xxx.Cells.Count).Activate
End Sub

At the moment it will treat all hyperlinks the same, that is making the last cell in each hyperlink the active cell.

I'm really not familiar with excels code and I wouldn't know where I'd put it or what areas in that code need fixed to what's in my sheet. And I'm trying to make the first cell in the hyperlink the active cell. I appreciate the help though

That works just fine for me in both Excel 2002 and 2007.

Did you delete the original non-formula hyperlink you were using?

yeah it's not workin for me. I even tried just typing in =HYPERLINK( then selected the 'extended info" page, and the range. Just not goin for me.

 

[T. Valko]

yeah it's not workin for me. I even tried just typing in =HYPERLINK( then selected the 'extended info" page, and the range. Just not goin for me.

Here's a small sample file that demonstrates this.

Hyperlink.xls 15kb

http://cjoint.com/?BBzaRZKplaI

 

[richh]

Thanks both of ya for the help; I'm gonna try to tackle this next Monday as my work day is over. If anything, I'll just have these links go to one cell rather than a range.

Thanks again!!