=(HYPERLINK) to auto generate list of links to another worksheet and display friendly names

[MissTiff]

I am not conversant with VBA so I want to use the =(HYPERLINK) feature to generate a list of links in Column A of my 'JOBLIST' worksheet with each row in Column A linking to Row 3 of every third column in a range of successive worksheets named 'Jobs_000_050'. 'Jobs_051_100', 'Jobs_101_151' etc, so the user can perform data entry into that cell. I wish to have only 50 Jobs on each of these worksheets to make the spreadsheet more manageable. The 'JOBLIST' worksheet will have 2000 Job Nos in Column A. In the worksheet 'Jobs_001_050' the data for each Job spans three columns per Job (Name, Date, Hours) with these headings in Row 2. Row 1 of the three columns for each Job (A1, B1, C1) will have 'Job No' (this is a text label), '1' (this is the Job No), '0.00' (this will be the SUM of the Hours entered in C3:C20 below). I would like the Job No displayed in the rows of Column A of my 'JOBLIST' worksheet, rather than the actual hyperlink address - I believe this is the 'friendly name' part of the =(HYPERLINK) syntax. Each successive Job No in the 'JOBLIST' worksheet would link to cells A3, D3, G3 etc of the relevant 'Jobs_###_###' worksheet. In addition to this, I would like each Job No in the 'Jobs_###_###' worksheet to link back to the relevant row in the 'JOBLIST' worksheet as it contains other data for each Job. Again, I would like these hyperlinks to display the Job No as the 'friendly name' rather than the hyperlink address'.

I have another 'LINKS' worksheet in the spreadsheet which contains columns with the worksheet name, the cell address for the hyperlink, and the friendly name, i.e. Column A would have fifty rows with Jobs_001_050 followed by fifty rows with Jobs_051_100 and so on. Column B would have B3, E3, H3, K3 etc. Column C would have 1, 2, 3, 4 etc. Can I build a formula to combine this data to create the hyperlinks with friendly names for Column A of my JOBSLIST worksheet? From what I understand, when I combine the elements from Columns A, B and C of my 'LINKS' worksheet (using =A1&B1&C1) I will also need to add sections of syntax to enclose the address in single quotes followed by an exclamation mark and to enclose the friendly name in square brackets. I would appreciate it a great deal if anybody can advise me, as it will otherwise take me weeks to manually create each link using 'Insert Hyperlink' and 'Text to Display'.

 

[pbornemeier]

Welcome to the Board!

Since you will have 2000 jobs, do you want the references to be XXXX-XXXX for the numbers from 1 to 999?

 

[pbornemeier]

Please place this code in a standard module in an empty worksheet and run the Main procedure.
Let me know if it sets up the worksheets and hyperlinks as you specified, and if it does not meet your needs, I can work with the HYPERLINK formulas to get the same result.

DO NOT RUN THIS CODE IN THE WORKSHEET WITH YOUR DATA WORKSHEETS

Option Explicit

Sub Main()

    Dim iAnswer As VbMsgBoxResult
    
    iAnswer = MsgBox("This code should only be run in an empty worksheet.  Do you wish to continue?", vbDefaultButton2 + vbOKCancel, "Continue?")
    
    If iAnswer = vbOK Then
        CreateJOBLIST
        CreateLINKS
        Create40JobWorksheets
        AddHyperlinksToJOBLIST
    End If
    Worksheets("Joblist").Select
    
End Sub

Sub CreateJOBLIST()

    RecreateWorksheet "JOBLIST"
        
    With Worksheets("Joblist")
        With .Range("A1:A2000")
            .FormulaR1C1 = "=RIGHT(""000"" &ROW(),4)"
            Application.Calculate
            .NumberFormat = "@"
            .Value = .Value
        End With
    
    End With
    
End Sub

Sub CreateLINKS()

    
    Dim lX As Long
    
    RecreateWorksheet "LINKS"
    
    With Worksheets("LINKS")
        For lX = 1 To 40
            .Range(.Cells(50 * (lX - 1) + 1, 1), .Cells(50 * (lX), 1)).Value = _
                "Jobs_" & Right("0000" & 50 * (lX - 1) + 1, 4) & "_" & Right("0000" & 50 * (lX), 4)
        Next
        
        With .Range("B1:B50")
            .FormulaR1C1 = "=ADDRESS(3,(3*ROW())-1,4)"
            Application.Calculate
            .Value = .Value
            .Copy Destination:=Range("B51:B2000")
        End With
        
        .Range("C1").FormulaR1C1 = "1"
        .Range("C1").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
            Step:=1, Stop:=2000, Trend:=False
        .UsedRange.Columns.AutoFit
    End With

End Sub

Sub Create40JobWorksheets()

    Dim lX As Long, lY As Long
    
    Dim sWorksheetName As String
    Dim sRef As String
    
    Application.ScreenUpdating = False
    For lX = 1 To 40
        sWorksheetName = "Jobs_" & Right("0000" & 50 * (lX - 1) + 1, 4) & "_" & Right("0000" & 50 * (lX), 4)
        RecreateWorksheet sWorksheetName
        With Worksheets(sWorksheetName)
            With .Range("A1:C1")
                .Value = Array("Job No", "X", "=SUM(C3:C20)")
                .Copy Destination:=Range("D1:ET1")
            End With
            With .Range("A2:C2")
                .Value = Array("Name", "Date", "Hours")
                .Copy Destination:=Range("D2:ET2")
            End With
            For lY = 1 To 50
                With .Cells(1, 3 * (lY - 1) + 2)
                    .NumberFormat = "@"
                    .Value = Right("000" & 50 * (lX - 1) + lY, 4)
                End With
                sRef = "!" & .Cells(50 * (lX - 1) + lY, 1).Address(False, False)
                ActiveSheet.Hyperlinks.Add Anchor:=.Cells(1, 3 * (lY - 1) + 2), Address:="", _
                    SubAddress:="JOBLIST" & sRef, TextToDisplay:=Cells(1, 3 * (lY - 1) + 2).Value
                .Columns(1 + (3 * (lY - 1))).ColumnWidth = 25
             Next
        End With
    Next
    
    Application.ScreenUpdating = True
    
End Sub

Sub AddHyperlinksToJOBLIST()

    Dim lX As Long, lY As Long
    Dim sRef As String
    Dim sWorksheetName As String
    
    With Worksheets("Joblist")
        For lX = 1 To 40
            For lY = 1 To 50
                sWorksheetName = "Jobs_" & Right("0000" & 50 * (lX - 1) + 1, 4) & "_" & Right("0000" & 50 * (lX), 4)
                sRef = "!" & Cells(3, 3 * (lY - 1) + 2).Address(False, False)
                ActiveSheet.Hyperlinks.Add Anchor:=.Cells(50 * (lX - 1) + lY, 1), Address:="", _
                    SubAddress:=sWorksheetName & sRef, TextToDisplay:=.Cells(50 * (lX - 1) + lY, 1).Value
            Next
        Next
    End With
End Sub

Sub RecreateWorksheet(sWorksheet As String)

    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets(sWorksheet).Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = sWorksheet
    
End Sub

 

[MissTiff]

Hi,

Thank you pbornemeier, but as I said at the start of my initial post, I am not conversant with VBA at all and would prefer to use =(HYPERLINK) not to have to use VBA code. Through a lot of trial and error, I do seem to have it all working now. I made a separate sheet 'LINKLIST' and generated two columns there - one with the actual target cell address (A1:A2000), and one with the friendly name which I wanted to display (B1:B2000) and copied them as hidden columns to my JOBLIST sheet. The cells in column A have =HYPERLINK(B1,C1) etc and it all seems to be working wonderfully. In the worksheets 'Jobs_001_050' etc, I have just inserted two hidden rows below Row 1 and copied and transposed the cell addresses into Row 2 and the friendly names into Row 3, with the cell in Row 1 which needs to link back to the JOBLIST sheet having =HYPERLINK(B2,B3) and again, this seems to be working perfectly for me.

Thank you for your trouble anyway.

Regards,
Tiff