Hyperlink to defined cell - 1?

[gino59]

Hi all!

Just curious - is there a way to hyperlink to a cell (with a defined name) and then offset up 1 row (same column) as where the defined name cell is?

I have a defined cell called lastEntry on worksheet 3. This defined name cell moves with every entry made. From worksheet 1, I want to click the hyperlink and have it go directly to whatever cell lastEntry happens to be in and then move up 1 row in the same column (offset(lastEntry,-1,0).

Hope that's clear? Any suggestions?

Cheers,
Gino

 

[Jerry Sullivan]

Hi Gino,

One approach would be to define another named range that is one cell above lastEntry- then have your link address go to that cell instead.

Depending on the type of Hyperlink, you might be able do this through the FollowHyperlink Event...but the first option above would be much simpler with no VBA required.

 

[shg]

You could use the HYPERLINK function:

=HYPERLINK("#" & ADDRESS(ROW(lastEntry) - 1, COLUMN(lastEntry),,, "Sheet3"), "Link")

 

[gino59]

Thanks, Jerry - sounds easy enough but that just hyperlinks to that one cell. LastEntry moves down the table as entries are added so the actual cell reference might be L45 or L455 depending on the number of entries added each day.

I defined LastEntry as =OFFSET(Table1[[#Totals],[ActualCost]],-1,0).

I was hoping to configure the hyperlink to use that defined name which would position the active cell 1 row up (same column) in Table1 - regardless of what cell reference that happened to be.

What's interesting is that I can see the defined name, LastEntry in the Name Manager, but when I go to set the hyperlink, LastEntry isn't listed in the available Defined Names in the Edit Hyperlink pop-up.

Weird - and I'm sure this is something funky with Excel 2010, which I'm just starting to explore.

Thanks!
Gino

 

[Jerry Sullivan]

SHG has a good solution if you want to use the =HYPERLINK() function.

What I was suggesting was to define a range, say HLTarget as
HLTarget =OFFSET(Table1[[#Totals],[ActualCost]],-2,0).

This would allow the link to be dynamic as the table is resized.

 

[gino59]

Hi shg - just saw your post. I'm getting a "The text you entered is not a valid reference or defined name" error message.

=HYPERLINK("#" & ADDRESS(ROW(lastEntry) -1, COLUMN(lastEntry),,, "Sheet3"), "Link")

For now, I've got 3 sheets in the workbook. Sheet 1 should have the hyperlink button and sheet 2 should have the target. So, I changed
"Sheet3" to "Sheet2" and even tried "Sheet1" to no avail.

lastEntry is in the Defined Names list so I know that's good there.

Not sure and by the way, did you edit your post? I thought I sawa different formula a moment ago!!

Or it could be the end of the day...

 

[shg]

Not a button, just a plain old formula that goes in a cell.

did you edit your post?

Yup. It was

=HYPERLINK("#Sheet3!" & ADDRESS(ROW(lastEntry) - 1, COLUMN(lastEntry)), "Link")

Either works.

 

[gino59]

Funky - if I define a name such as

lastEntry
=OFFSET(Table1[[#Totals],[ActualCost]],-1,0) or even -2, I can't see that defined name in the Edit Hyperlink dialogue box.

But, if I define a name such as
ht
=Table1[[#Totals],[ActualCost]]
it shows in the Edit Hyperlink dialogue box.

Seems weird to me or I'm really missing the boat on that one.

I have a simle shape on the first sheet and when clicked, all I want it to do is take me to the last row last column used on sheet 2 and back up just one row in the same column. It would be even neater if I could figure out how to get it to insert a new row below the last entry (and before the total row in the table), but I'll have to break out my VBA book for that!!

Cheers!
Gino

 

[gino59]

Okay folks! It's working!! I must've been gaffing something up, but it's apparent the formula must only work in a cell. I was trying to stick it on a button and no go. Works great just sitting in a cell.

Sorry for the mass confusion and thank you again!

I may just get adventurous and wander into VBA - it would be slick if when the link was clicked it went to its spot and inserted a new row.

Fingers crossed!

Thanks again!

Cheers!
Gino