[Solved] Macros formula

G

Gredang

New member
Hi I need help here.

I have a workbook with 2 worksheets; worksheet 1 was given a name as “Names” and worksheet 2 was given a name as “Data”.

In worksheet 1, I have a list of data which consist of column A - Name, column B - Email address, column C - contact no, column D - Residential address. I have a total of 10 rows with these data. Each row in column A has a different name. Eg: Row A1 is Andy, Row A2 is Lisa, Row A3 is Tommy and so on until row 10. I wan to, when I click on row A1, Andy’s name, it will appeared all the details sheet 2. And if I click on Lisa, Lisa’s details will be shown in sheet 2. However I do not know how to put into macro formula. However, I have insert index match for all the columns except column A as I wan to click their name and it appeared in sheet 2 with the details. Pls kindly assist me. Thanks alot!
 
Gredang said:
Hi I need help here.

I have a workbook with 2 worksheets; worksheet 1 was given a name as “Names” and worksheet 2 was given a name as “Data”.

In worksheet 1, I have a list of data which consist of column A - Name, column B - Email address, column C - contact no, column D - Residential address. I have a total of 10 rows with these data. Each row in column A has a different name. Eg: Row A1 is Andy, Row A2 is Lisa, Row A3 is Tommy and so on until row 10. I wan to, when I click on row A1, Andy’s name, it will appeared all the details sheet 2. And if I click on Lisa, Lisa’s details will be shown in sheet 2. However I do not know how to put into macro formula. However, I have insert index match for all the columns except column A as I wan to click their name and it appeared in sheet 2 with the details. Pls kindly assist me. Thanks alot!
Click to expand...
Hello Gredang

Thanks for reaching out and posting your queries within the ExcelDemy Forum. You have two worksheets called Names and Data. In sheet Data, you have a list of data which consists of Names, Email Addresses, Contact No, and Residential Addresses. In sheet Names, you have ten rows of names. Each row in column A has a different name. You want to click on their name, which appears in Data with the details.

Steps:
Right-click on the Sheet-name tab >> go to View Code.
Insert a additional code in sheet names.png​
Next, insert the following code >> click on the Save icon.
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    Dim namesSheet As Worksheet
    Dim dataSheet As Worksheet
    Dim namesRange As Range
    Dim dataRange As Range
    Dim cell As Range
    Dim linkAddress As String
 
    Set namesSheet = ThisWorkbook.Sheets("Names")
    Set dataSheet = ThisWorkbook.Sheets("Data")
 
    Set namesRange = namesSheet.Range("A2:A" & namesSheet.Cells(Rows.Count, 1).End(xlUp).Row)
    Set dataRange = dataSheet.Range("A2:A" & dataSheet.Cells(Rows.Count, 1).End(xlUp).Row)
 
    If Not Intersect(Target, namesRange) Is Nothing Then

        For Each cell In namesRange
            If Target.Address = cell.Address Then
                dataSheet.Rows("2:" & dataSheet.Rows.Count).EntireRow.Hidden = True
                dataSheet.Rows(cell.Row).EntireRow.Hidden = False
                Exit For
            End If
        Next cell
 
    End If
 
End Sub
Paste code in sheet module and save..png​
Later, go to Insert >> click on Module >> insert the below code and Run.
Code: 
Sub AddHyperlinks()

    Dim namesSheet As Worksheet
    Dim dataSheet As Worksheet
    Dim namesRange As Range
    Dim dataRange As Range
    Dim cell As Range
    Dim linkAddress As String

    Set namesSheet = ThisWorkbook.Sheets("Names")
    Set dataSheet = ThisWorkbook.Sheets("Data")

    Set namesRange = namesSheet.Range("A2:A" & namesSheet.Cells(Rows.Count, 1).End(xlUp).Row)
    Set dataRange = dataSheet.Range("A2:A" & dataSheet.Cells(Rows.Count, 1).End(xlUp).Row)

    For Each cell In namesRange

        linkAddress = dataRange.Cells(cell.Row - 1).Address(external:=True)

        namesSheet.Hyperlinks.Add _
            Anchor:=cell, _
            Address:="", _
            SubAddress:=linkAddress, _
            TextToDisplay:=cell.Value

    Next cell

End Sub
Add Hyperlinks.png​
Output: You can an overview by clicking the following link.
I have attached the solution Workbook to help you understand better. Please let me know if you want to know how the formula works. Don't hesitate to contact us again if you have any more questions.

Regards
Lutfor Rahman Shimanto
 

Attachments

Lutfor Rahman Shimanto said:
Hello Gredang

Thanks for reaching out and posting your queries within the ExcelDemy Forum. You have two worksheets called Names and Data. In sheet Data, you have a list of data which consists of Names, Email Addresses, Contact No, and Residential Addresses. In sheet Names, you have ten rows of names. Each row in column A has a different name. You want to click on their name, which appears in Data with the details.

Steps:
Right-click on the Sheet-name tab >> go to View Code.
Next, insert the following code >> click on the Save icon.
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    Dim namesSheet As Worksheet
    Dim dataSheet As Worksheet
    Dim namesRange As Range
    Dim dataRange As Range
    Dim cell As Range
    Dim linkAddress As String
 
    Set namesSheet = ThisWorkbook.Sheets("Names")
    Set dataSheet = ThisWorkbook.Sheets("Data")
 
    Set namesRange = namesSheet.Range("A2:A" & namesSheet.Cells(Rows.Count, 1).End(xlUp).Row)
    Set dataRange = dataSheet.Range("A2:A" & dataSheet.Cells(Rows.Count, 1).End(xlUp).Row)
 
    If Not Intersect(Target, namesRange) Is Nothing Then

        For Each cell In namesRange
            If Target.Address = cell.Address Then
                dataSheet.Rows("2:" & dataSheet.Rows.Count).EntireRow.Hidden = True
                dataSheet.Rows(cell.Row).EntireRow.Hidden = False
                Exit For
            End If
        Next cell
 
    End If
 
End Sub
Later, go to Insert >> click on Module >> insert the below code and Run.
Code: 
Sub AddHyperlinks()

    Dim namesSheet As Worksheet
    Dim dataSheet As Worksheet
    Dim namesRange As Range
    Dim dataRange As Range
    Dim cell As Range
    Dim linkAddress As String

    Set namesSheet = ThisWorkbook.Sheets("Names")
    Set dataSheet = ThisWorkbook.Sheets("Data")

    Set namesRange = namesSheet.Range("A2:A" & namesSheet.Cells(Rows.Count, 1).End(xlUp).Row)
    Set dataRange = dataSheet.Range("A2:A" & dataSheet.Cells(Rows.Count, 1).End(xlUp).Row)

    For Each cell In namesRange

        linkAddress = dataRange.Cells(cell.Row - 1).Address(external:=True)

        namesSheet.Hyperlinks.Add _
            Anchor:=cell, _
            Address:="", _
            SubAddress:=linkAddress, _
            TextToDisplay:=cell.Value

    Next cell

End Sub
Output: You can an overview by clicking the following link.
I have attached the solution Workbook to help you understand better. Please let me know if you want to know how the formula works. Don't hesitate to contact us again if you have any more questions.

Regards
Lutfor Rahman Shimanto
Click to expand...
Hi Lutfor,

thanks for assisting. I think my real piece is abit complex. I have tried to change the necessary but not able to work. Pls assist me again.
Ry much appreciated.
 

Attachments

Last edited:
Hi Lutfor I really like ur formula and that’s actually what I want! I thought that with that formula help I am able to try to resolve my problem (by trial and errors) who knows my file is really too complex to settle it.

Are u able to assist me on this? Thanks a lot!
 
Gredang said:
Hi Lutfor,

thanks for assisting. I think my real piece is abit complex. I have tried to change the necessary but not able to work. Pls assist me again.
Ry much appreciated.
Click to expand...
Lutfor Rahman Shimanto said:
Hello Gredang

Thanks for reaching out and posting your queries within the ExcelDemy Forum. You have two worksheets called Names and Data. In sheet Data, you have a list of data which consists of Names, Email Addresses, Contact No, and Residential Addresses. In sheet Names, you have ten rows of names. Each row in column A has a different name. You want to click on their name, which appears in Data with the details.

Steps:
Right-click on the Sheet-name tab >> go to View Code.
Next, insert the following code >> click on the Save icon.
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    Dim namesSheet As Worksheet
    Dim dataSheet As Worksheet
    Dim namesRange As Range
    Dim dataRange As Range
    Dim cell As Range
    Dim linkAddress As String
 
    Set namesSheet = ThisWorkbook.Sheets("Names")
    Set dataSheet = ThisWorkbook.Sheets("Data")
 
    Set namesRange = namesSheet.Range("A2:A" & namesSheet.Cells(Rows.Count, 1).End(xlUp).Row)
    Set dataRange = dataSheet.Range("A2:A" & dataSheet.Cells(Rows.Count, 1).End(xlUp).Row)
 
    If Not Intersect(Target, namesRange) Is Nothing Then

        For Each cell In namesRange
            If Target.Address = cell.Address Then
                dataSheet.Rows("2:" & dataSheet.Rows.Count).EntireRow.Hidden = True
                dataSheet.Rows(cell.Row).EntireRow.Hidden = False
                Exit For
            End If
        Next cell
 
    End If
 
End Sub
Later, go to Insert >> click on Module >> insert the below code and Run.
Code: 
Sub AddHyperlinks()

    Dim namesSheet As Worksheet
    Dim dataSheet As Worksheet
    Dim namesRange As Range
    Dim dataRange As Range
    Dim cell As Range
    Dim linkAddress As String

    Set namesSheet = ThisWorkbook.Sheets("Names")
    Set dataSheet = ThisWorkbook.Sheets("Data")

    Set namesRange = namesSheet.Range("A2:A" & namesSheet.Cells(Rows.Count, 1).End(xlUp).Row)
    Set dataRange = dataSheet.Range("A2:A" & dataSheet.Cells(Rows.Count, 1).End(xlUp).Row)

    For Each cell In namesRange

        linkAddress = dataRange.Cells(cell.Row - 1).Address(external:=True)

        namesSheet.Hyperlinks.Add _
            Anchor:=cell, _
            Address:="", _
            SubAddress:=linkAddress, _
            TextToDisplay:=cell.Value

    Next cell

End Sub
Output: You can an overview by clicking the following link.
I have attached the solution Workbook to help you understand better. Please let me know if you want to know how the formula works. Don't hesitate to contact us again if you have any more questions.

Regards
Lutfor Rahman Shimanto
Click to expand...
Hi Lutfor I really like ur formula and that’s actually what I want! I thought that with that formula help I am able to try to resolve my problem (by trial and errors) who knows my file is really too complex to settle it.

Are u able to assist me on this? Thanks a lot!
 
Hello Gredang,
Thanks for sharing your feedback. After going through your workbook, I understand that you want your “names” sheet to be anchored to the “data” sheet so that when you click on any trainee’s name in the “names” sheet, you will be able to see that trainee’s details in “data” sheet.
To accomplish this we can run a VBA event macro. Right-click on the “names” sheet tab and select the View Code option.
2tr4a7TinNi8VtsKe3draTNzJGe-zhJ9gMrSWCsWlu_14aar4nphoFUK7npQq3Rb8upni-bP3J5C-YAynwAemCLjmtbq8NAXfLDPixgfu21G9ocvvKYjxYA2dMrIKQDDz3L8HRQLQBN1DSSV-BKQS3U
Afterward, enter the following VBA Code.

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Set namesSheet = ThisWorkbook.Sheets("names")
    Set dataSheet = ThisWorkbook.Sheets("data")
    
    Set colHeaders = namesSheet.Range("A5:BL5")
    Dim lastRow As Integer
    lastRow = namesSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Set traineeData = namesSheet.Range("A6:BL" & lastRow)
    Set traineeNames = namesSheet.Range("A6:A" & lastRow)
    
    Dim i As Integer
    Dim j As Integer
    Dim emptyRows As Integer
    emptyRows = 4
    
    If Not Intersect(Target, traineeNames) Is Nothing Then
        targetRow = Split(Target.Address, "$")(2)
        Set targetTraineeData = traineeData.Rows(targetRow - emptyRows - 1)
        
        Set firstDataRange = dataSheet.Range("A2:C42")
        
        For i = 1 To firstDataRange.Rows.Count
            If firstDataRange.Cells(i, 1) <> "" Then
                For j = 1 To colHeaders.Columns.Count
                    If colHeaders.Cells(1, j).Value = firstDataRange.Cells(i, 1) Then
                        Exit For
                    End If
                Next j
                firstDataRange.Cells(i, 3).Value = targetTraineeData.Cells(1, j).Value
            End If
        Next i
        
        Set secondDataRange = dataSheet.Range("E2:F42")
        
        For i = 1 To secondDataRange.Rows.Count
            If secondDataRange.Cells(i, 1) <> "" Then
                For j = 1 To colHeaders.Columns.Count
                    If colHeaders.Cells(1, j).Value = secondDataRange.Cells(i, 1) Then
                        Exit For
                    End If
                Next j
                secondDataRange.Cells(i, 2).Value = targetTraineeData.Cells(1, j).Value
            End If
        Next i
        
        Set thirdDataRange = dataSheet.Range("I2:M42")
        For i = 1 To thirdDataRange.Rows.Count
            If thirdDataRange.Cells(i, 1) <> "" Then
                For j = 1 To colHeaders.Columns.Count
                    If colHeaders.Cells(1, j).Value = thirdDataRange.Cells(i, 1) Then
                        Exit For
                    End If
                Next j
                If (i = 31) Or (i = 39) Then
                    thirdDataRange.Cells(i, 3).Value = targetTraineeData.Cells(1, j).Value
                Else
                    thirdDataRange.Cells(i, 5).Value = targetTraineeData.Cells(1, j).Value
                End If
            End If
        Next i
        
        Set fourthDataRange = dataSheet.Range("N2:P42")
        For i = 1 To fourthDataRange.Rows.Count
            If fourthDataRange.Cells(i, 1) <> "" Then
                For j = 1 To colHeaders.Columns.Count
                    If colHeaders.Cells(1, j).Value = fourthDataRange.Cells(i, 1) Then
                        Exit For
                    End If
                Next j
                fourthDataRange.Cells(i, 3).Value = targetTraineeData.Cells(1, j).Value
            End If
        Next i
        
        dataSheet.Activate
        
    End If
End Sub
Click on the Save icon. Return to the “names” sheet and click on any Trainee’s name.
RElsmYKjD18YqLNcRRGkobta0NvUjrFVbuEfcYDTsoIXn94WxfCwxJN5deJZL28Xk8X5T5sgoEUEV5SskRVeA5SoqTz-iSH2qBSbj0MlkUp8NjHnzyE2KKQe92cWYTB1LNDgMzDBopNpTKFtggPaD34
This will take you to the “data” sheet where you will see the details of the selected trainee.
P49Ga3wXcHvAVz8t01yapsWhCNRKEJJkYcyM6n0sZAn0yz53Uxkkkvn7J71rlcdUzOmNCuTPOf17UgzF3VmZ_gOPVAAY2pHPzc7v6wJLITmtaAgJPn1kk90xBAvjga609MwR3kTzXqnYfuNhIYJ1XTg
However, if you click on any other cell other than the Trainee’s name cells, nothing will occur.

The Excel Workbook I used is attached below.

Hopefully, I was able to resolve your problem. Let us know your feedback.

Regards,
Seemanto Saha
ExcelDemy
 

Attachments

You must log in or register to reply here.

Online statistics

Members online
0
Guests online
1,314
Total visitors
1,314
Totals may include hidden visitors.

Forum statistics

Threads
456
Messages
2,020
Members
1,886
Latest member
taixiuonlinecab

Share this page

Back
Top