Hyperlink to first blank cell in column?

  • Thread starter Thread starter Ed from AZ
  • Start date Start date
E

Ed from AZ

(XL2007) Thanks to help from MVP Biff, I can return the row number of
the first blank cell in a single-column named range using an array
formula (http://tinyurl.com/qb689k). This is a dynamic range that
will adjust as new items are added.

I'm setting up a workbook in which I have a "Blank Master" sheet and a
"Jobs List" sheet. The "Master" sheet will be copied and renamed for
each month. I'd like to have a hyperlink on the "Master" sheet that
will carry over to each new copied sheet that would take the user to
the first blank cell in the JOBS named range on the "Jobs List"
sheet.

As far as I can see, that means I would have to use a formula as the
Hyperlink reference. But it's not working. Do I have the mechanics
incorrect? Or is this just not possible?

Ed
 
Maybe just a regular link to the JOBS sheet, then a Worksheet_Activate macro
active ON that sheet that always jumps you to the next empty row in a
particular column?

Private Sub Worksheet_Activate()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & LR).Select
End Sub

Would that work for you?
 
Thanks for the reply. I'm trying to stay away from macros here if at
all possible.

I've thought about using a dynamic named range for that first blank
cell and just hyperlinking to that name. But dynamic names don't show
up in the hyperlink dialog, and it throws an invalid reference error
when I try to use it.

This may just not work, and I'll have to come up with a Plan B. Oh,
well.
Ed
 
Fixed it, but I'm not really sure exactly how!!

On the sheet listing the jobs, I have a cell with an array formula
that returns the row number of the first empty cell in my dynamic
named range. On the master sheet, I put
=HYPERLINK("[BookName.xlsx]"&NamedRange,"Click here")

It does work! And that's what counts!!
Ed
 
Back
Top