Can I hyperlink to the last entry of a sheet?

B

Bon Rouge

Can I hyperlink to the last entry of a sheet?

The way I'm doing it now the sheets I link to will open up at the to
of the sheet, but if I don't hyperlink they open at the last entry
I'd like to go there through a hyperlink.
Is this possible?

Thanks for your help
 
D

Dave Peterson

I depended on the fact that column A always had something in it if the row was
used.

I used Sheet1 as my worksheet.

I added a worksheet name (insert|name):
Names in Workbook: Sheet1!LastCell
(the sheet name is important if you want to use the same name (LastCell) for
other sheets.

Then in the refers to box:
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0)

Then off to someplace to add an =hyperlink() worksheet function.

I used a cell in Sheet2:

=HYPERLINK("#"&CELL("address",Sheet1!lastcell),"clickme")

I clicked the "clickme" and off I went to the lastcell in column A of sheet1.
 
B

Bon Rouge

Hi Dave,

I appreciate this help and I really want to use it, but I'm really ne
to Excel (I'd only used the simplest worksheets before).

Can I ask you to explain a couple of things?
"I added a worksheet name (insert|name):"
Click to expand...

You mean you named the sheet? OK. I've got that.
"Names in Workbook: Sheet1!LastCell"
Click to expand...

Now, I'm not sure what this is here. You put this in a cell? Exactl
those words?
"(the sheet name is important if you want to use the same nam
(LastCell) for other sheets.

Then in the refers to box:

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0)
Click to expand...

What or where is the 'refers to box'?
"Then off to someplace to add an =hyperlink() worksheet function.

I used a cell in Sheet2:

=HYPERLINK("#"&CELL("address",Sheet1!lastcell),"clickme")"

I clicked the "clickme" and off I went to the lastcell in column A o
sheet1."
Click to expand...

I think I've got this last bit.

Sorry about being a bit slow.

I appreciate your help
 
D

Dave Peterson

Look at the worksheet menubar: File|Edit|view|Insert...

That's where this Insert|name dialog is found.

Then you'll see the other stuff.
 
B

Bon Rouge

Thanks again.
I've just tried what you said, but it said the name was not valid.
What might be the problem
 
D

Dave Peterson

What name did you use?

What's the name of the worksheet?

If the worksheet had spaces, you'll have to use something like:

'sheet 1'!lastcell
 
B

Bon Rouge

Hey Dave,

I've finally figured out what's going on there and I got one of th
cells in my index to link to somewhere near the bottom of the correc
sheet. Great!

My problem now is kind of complicated... (well, it's confusing m
anyway).

In column A of the index I have written the sheet names - these ar
short, like 'Aki C' - short for 'Akiko Chiba' (I have an English schoo
in Japan - these are our students).

In column B I have a formula that shows the full name of the student
by taking it from A1 of the student's file : =INDIRECT("'" & A8
"'!A1")
I linked all of these individually to each sheet with the right-clic
Hyperlink option (I just chose the sheet from the list). This took age
and I'm sure there was a faster way to do it, but I didn't know what i
was.

So now, with your help, I have this:
=HYPERLINK("#"&CELL("ADDRESS",'Ai S'!Lastcell),INDIRECT("'" & A2
"'!A1"))

This shows "Ai Sugiyama" and gives me a hyperlink to the bottom of he
sheet.
Of sourse to do this I had to enter the name and reference as you tol
me. This took a while - I wasn't even able to paste it in.

To get what I want, it seems to me that I have to do the same think fo
the other 200 students and change the link text for every one. Quite
lengthy job...

Is there an easier way? I think there must be, but I have no idea wha
it could be.

I appreciate your patience and help.

Stev
 
D

Dave Peterson

How about adding another helper cell (column C???) and using a formula like:

=HYPERLINK("#'"&A8&"'!lastcell","clickme")
(in C8)

And to add the LastCell names to all the worksheets -- except the Index
worksheet (I used "Index" for that name), you could run a small macro:

Option Explicit
Sub testme01()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = Worksheets("Index").Name Then
'do nothing
Else
wks.Names.Add Name:="LastCell", _
RefersTo:="=OFFSET('" & wks.Name _
& "'!$a$1,COUNTA('" & wks.Name & "'!$a:$a)-1,0)"
End If
Next wks

End Sub

And if you're working with names a lot (and you are now!), do yourself a big
favor and download:

Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager.

You can find it at:
NameManager.Zip from http://www.bmsltd.ie/mvp

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Notice that I didn't use the original formula in my hyperlink:
=HYPERLINK("#"&CELL("address",Sheet1!lastcell),"clickme")

By using &cell() stuff, this formula reacts very nicely when you change the name
of the worksheet. But I'm betting that your worksheets don't change names (else
your formula in B breaks, too).
 
B

Bon Rouge

Hey Dave,
Thanks a lot for your help. This is great.
I've actually used what you gave me without creating an extra column b
doing this in column B :

=HYPERLINK("#'"&A2&"'!lastcell",INDIRECT("'" & A2 & "'!A1"))

I ran the macro and that was great - I've never used macros before
Thanks for your guidance.

I've also installed the name manager. It looks great, but of course
I've got no idea how or why I might use it. Maybe I'll come back soo
and ask you about something else...

I do have one other small problem though if you have time - I'
guessing this won't be a big problem :

Now I have all those links to the bottom of the sheets just as
wanted, but the active cell is always two above the last non-blan
cell. How can I jump to the cell below the last non-blank cell ie. th
next cell I want to write in? (Is this clear enough...?)

And one more thing...

I guess I have to press RUN for the macro each time I get a ne
student? (That's quite often).

Thanks again. I'd like to buy you a beer
 
B

Bon Rouge

OK. I figured out how to go to the cell I want.

I changed this :


& "'!$a$1,COUNTA('" & wks.Name & "'!$a:$a)-1,0)"

to this :


& "'!$a$1,COUNTA('" & wks.Name & "'!$a:$a)+2,0)"

ie. '-1' to '+2
 
D

Dave Peterson

That name uses the offse worksheet function. And inside that offset function,
you'll see =counta().

=CountA() counts the number of non-empty (including formulas) in that range.

So my bet is you have at two cells that are empty.

I think you have two choices--fill up those cells. I like to use a formula that
makes the cell look empty, but weird enough to make me remember why I used it:

=""

is my choice.

Or you could adjust the formula to add 2.

And I just looked at your other post--and you've got that.

And you could either run the macro, or just do an Insert|name and type in that
formula again--for the new student.

(You could use the name manager to verify that you did things correctly.)
 
Top