Help hyperlink

lodk · December 4th, 2014, 10:41 AM

Hello, I am having a bit of a problem. I am a self taught excel user and learn as I go. I keep spreadsheets for a library that I work at. It is more of a hobby then a work requirement. Anyway I have a bunch of (book)collections that keep track of, roughly about 50 crates of 20 books each.
My question is if I have a spreadsheet with say 51 sheets each except for the first (this first one one would be labeled "schools" labeled "crate 1", "crate 2", "crate 3", and so on. how can I make it so if I have column A1="school name" and column b1=the "crate#" it would make a hyperlink to the sheet named after the crate number in the cell of b1. to make it harder I need it to be based of the text in the cell because it changes every week so the hyperlink needs to update if I move "crate 1" from "school 1" to "school 2" because "school 1" will now have "crate 2" and I need the hyperlink to go to "crate 2" now and not "crate 1".
I hope I explained it ok if not I can try to answer any questions. it would just make my record keeping so much sampler. thank you.

Shasur · December 5th, 2014, 03:16 AM

Hi

I hope you had your hands dirty with VBA too. The following snippet does a bit of what you want

Code:

For i = 3 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 2), Address:="", SubAddress:= _
      ActiveSheet.Cells(i, 1).Value & "!A1", TextToDisplay:="Click..."
      Next i
End Sub

Cheers
Shasur