Extract the correspoiding data from the database on clicking the mouse button in a specific cell

[Harish Sharma]

Hi All,

Is it possible to extract data "through excel formula" from the database on clicking mouse button in a specific cell?

I need help for extracting the data from the database, file is attached herewith.

I have a workbook containing one sheet "Data" which has three part :-
1. Database
2. Summary
3. Extracted Data (Problem area)

When i Click on any of the titles under the "Post" column (Srl no. 1 to 13) the corresponding data can be extracted in "Post wise Data" Table.

Thanks

 

[Abdul Matheen]

Hi Harish,

I got your query, yes you can do that data extract. There are different approaches to do this,

1st approach - The main data i think is coming from access, you just create access queries in the database base on the post and then create a link in excel vba to get the queries data when you click the particular post.

2nd approach - create a VBA code to get the data as the User clicks the psot.

and so on.......

Try this if you know the VBA or let me know i will do it for you.


Regards
Abdul Matheen

 

[SirJB7]

Hi, Harish Sharma!

Give a look at this file:
https://dl.dropboxusercontent.com/u... Data (for Harish Sharma at chandoo.org).xlsm

It has the following code for the selection change event of worksheet data, and uses 3 named ranges, one for each table (either structured or standard):

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' constants
    Const ksDB = "Table2"
    Const ksS = "SummaryTable"
    Const ksPWD = "PWDataTable"
    Const kiDB = 3
    Const kiS = 2
    ' declarations
    Dim rngDB As Range, rngS As Range, rngPWD As Range
    Dim I As Integer, J As Integer, K As Integer, A As String
    ' start
    Set rngS = Range(ksS)
    If Application.Intersect(rngS, Target) Is Nothing Or _
      Target.Cells.Count > 1 Then GoTo Worksheet_SelectionChange_Exit
    Set rngDB = Range(ksDB)
    Set rngPWD = Range(ksPWD)
    ' process
    A = rngS.Cells(Target.Row - rngS.Row + 1, kiS).Value
    With rngPWD
        .ClearContents
        I = 0
        For J = 1 To rngDB.Rows.Count
            If rngDB.Cells(J, kiDB).Value = A Then
                I = I + 1
                For K = 1 To rngDB.Columns.Count
                    .Cells(I, K).Value = rngDB.Cells(J, K).Value
                Next K
            End If
        Next J
    End With
    ' end
    Set rngPWD = Nothing
    Set rngDB = Nothing
Worksheet_SelectionChange_Exit:
    Set rngS = Nothing
End Sub

Just advise if any issue.

Regards!

 

[Harish Sharma]

Hi Harish,

I got your query, yes you can do that data extract. There are different approaches to do this,

1st approach - The main data i think is coming from access, you just create access queries in the database base on the post and then create a link in excel vba to get the queries data when you click the particular post.

2nd approach - create a VBA code to get the data as the User clicks the psot.

and so on.......

Try this if you know the VBA or let me know i will do it for you.


Regards
Abdul Matheen

Abdul Sir,

Thanks for your reply.

The Exact Solution of my problem has been posted by @ SirJB7. It is working perfectly.

Thanks a lot once again.

Harish

 

[Harish Sharma]

Hi, Harish Sharma!

Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Extract the correspoiding data from the database on clicking the mouse button in a specific cell - Shortlisted Data (for Harish Sharma at chandoo.org).xlsm

It has the following code for the selection change event of worksheet data, and uses 3 named ranges, one for each table (either structured or standard):

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' constants
    Const ksDB = "Table2"
    Const ksS = "SummaryTable"
    Const ksPWD = "PWDataTable"
    Const kiDB = 3
    Const kiS = 2
    ' declarations
    Dim rngDB As Range, rngS As Range, rngPWD As Range
    Dim I As Integer, J As Integer, K As Integer, A As String
    ' start
    Set rngS = Range(ksS)
    If Application.Intersect(rngS, Target) Is Nothing Or _
      Target.Cells.Count > 1 Then GoTo Worksheet_SelectionChange_Exit
    Set rngDB = Range(ksDB)
    Set rngPWD = Range(ksPWD)
    ' process
    A = rngS.Cells(Target.Row - rngS.Row + 1, kiS).Value
    With rngPWD
        .ClearContents
        I = 0
        For J = 1 To rngDB.Rows.Count
            If rngDB.Cells(J, kiDB).Value = A Then
                I = I + 1
                For K = 1 To rngDB.Columns.Count
                    .Cells(I, K).Value = rngDB.Cells(J, K).Value
                Next K
            End If
        Next J
    End With
    ' end
    Set rngPWD = Nothing
    Set rngDB = Nothing
Worksheet_SelectionChange_Exit:
    Set rngS = Nothing
End Sub

Just advise if any issue.

Regards!

My dear Respected @ SirJB7,

Thanks a lot for your response, and the magical solution suggested by you.

I am trying to learn VBA, and going to implement in my original sheet. if there is any issue i will back to you sir.

One small question is there.

Can we capture a Cell address through Excel Formula when we click anywhere in the worksheet apart from the capturing cell?

Thanks once again sir.

Harish

 

[SirJB7]

Hi, HarishSharma!

About your original requirement: Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

About the new question: What do you mean by cell address? Ok, we both know what a cell address is, there's no way to get access to the VBA equivalent of ActiveCell object and its properties, e.g., .Address, from a formula.

Regards!

 

[Harish Sharma]

Hi, HarishSharma!

About your original requirement: Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

About the new question: What do you mean by cell address? Ok, we both know what a cell address is, there's no way to get access to the VBA equivalent of ActiveCell object and its properties, e.g., .Address, from a formula.

Regards!

To take an Expert advice is like a “written commitment from the God OR a bow’s arrow, which never missed out its target”.

I mean to say that:
@ SirJB7 Now I am sure that I didn’t miss out any formula “from the ocean Excel formulae” that returns the Active Cell Address or its Reference, which I am searching since long. It is possible only through VBA.

Thanks once again sir @ SirJB7 for spearing your valuable time for me.

 

[NARAYANK991]

Hi Harish ,

I do not know why you want to know the cell address by clicking on a worksheet cell , especially through a formula ; however , in case you do not know this , try putting the formula :

=CELL("address")

in any unused cell on the worksheet.

Now , click on any other cell , and press F9 ; the cell which has the above formula will display the cell address where you clicked the mouse.

The problem is that each time you select a cell , you need to press F9 for the formula to recalculate.

Narayan

 

[Harish Sharma]

Hi Harish ,

I do not know why you want to know the cell address by clicking on a worksheet cell , especially through a formula ; however , in case you do not know this , try putting the formula :

=CELL("address")

in any unused cell on the worksheet.

Now , click on any other cell , and press F9 ; the cell which has the above formula will display the cell address where you clicked the mouse.

The problem is that each time you select a cell , you need to press F9 for the formula to recalculate.

Narayan

Narayan Sir,

Once again my previous message's line "To take Expert advice ----------" is come into the force.

I have tried this CELL () function but always forgot to press F9. "अगर मैं ये कहुँ कि मुझे पता ही नहीं था तो अतिसंयोक्ति नहीं होगी"

Thanks for your valuable remark on the topic.