Hyperlink Formulation

[Misti Kallas]

Good afternoon Gentleman,
I am in charge of data entry on a mill production spreadsheet, it was set up for daily entries for day and night shift for an entire year. My first column is the line description for rows. At some point, I will have to scroll forever to get to the exact date I need for my data entry. I have inserted a lightning bolt symbol just below the first column, linking a hyperlink to a particular cell to get me over to the remaining days of December 2016 data entry. However, I would love to edit the hyperlink to make it go the first blank cell, usually row 4 of the next column after I entered data. Some days, I do not enter production run for a shift or the day, so those days are left blank. The last two rows are my running total rows. What would be the best way achieve this? If someone could just tell me the proper terminology to "Google" or any other suggestions, I would greatly appreciate it. I have some coding experience but am still new to all of this. Thank you!

 

[p45cal]

I'm not at all sure of which cell you want to hyperlink to but it's going to change as you work.
For now I'll call it the first blank cell after any data in row 4 of the active sheet.

First, there's a problem with hyperlinks to dynamic named ranges from shapes in Excel, but we'll get round that later.

Solution 1.
In the attached in cells A22 on both relevant sheets, there's a hyperlink formula:
=HYPERLINK("#Lastcellinrow4","Go There")
Clicking on the text therein will cause a jump to the appropriate cell n row 4 on that sheet.

There's a defined name in the workbook called Lastcellinrow4 which has the definition:
=OFFSET(!$A$4,0,MAX(IF(LEN(!$4:$4)>0,COLUMN(!$4:$4))))

Those exclamation marks make it relevent to the active sheet, whichever that is at the time.

So that's one solution, but it doesn't use your lightning bolt.

Solution 2.
To do this we have to edit temporarily the Name to a fixed range, so keep a copy of the original formula somewhere safe so that you can paste it in again later.

Once the Name is a fixed range, adding/editing a hyperlink of a shape will allow you to select that defined name. Once you've done that, you should go back into the name definitions and paste your dynamic formula back in.

Now clicking on your lightning bolts will jump to the right cell in row 4 of the active sheet.

 

[Misti Kallas]

Thank you! This is perfect! I knew it would be something simple. I asked our HR manager for help on this. When I told her I wanted to use a symbol to create a link, she said she used the same symbol to create hers. However, when she tried to show me how it did not work on my spreadsheet. She used a whole other set of formulas. I like the "Go There" much better than the lightning bolt. I have played around in the Formulas tab > Name Manager > Define Name > Use in Formula to get acquainted with how you set up the formulas. I will continue to check on this site to help educate me on how to use Excel more efficiently. Once again, Thank you for all of your help. Have a great day!

 

[bosco_yip]

Another Option.

Hyperlink to Row 4 last cell without Define Name.

In A20, enter formula :

=HYPERLINK("#R4C"&MATCH("zzz",$4:$4)+1,"Goto There")

Regards
Bosco