Excel database modification

[Charmwah]

Hi all

I have an existing database in a worksheet, with customers arranged down the rows, and variables for the customers arranged along the columns. I then have a separate worksheet which acts as an 'At-a-glance' page whereby you choose the customer from a drop down list and a series of cells with INDEX & MATCH formulas retrieve the information for that customer from the database.

So far so good. What i'd then like to be able to do is, using the 'At-a-glance' page, let the user modify the result of the retrieved data (either in the same cell, or an adjacent cell) and then to copy that modified result back to overwrite the database.

Is this even possible? Can anybody think of a sensible way to do this? I presume it would involve some sort of macro but i'm not sure where to start...

Thanks in advance!

 

[mumps]

I think you would need a macro to do what you want. You would also need a unique identifier for each customer such as a customer ID. Can you post a screen shot of what your data on both sheets looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Charmwah]

Hi Mumps

Thanks very much for your response. Below is a link for the basic file, i've simplified it down to two worksheets...

https://www.dropbox.com/s/zucafxp8tjn81jq/Example.xlsx?dl=0

Kind regards

Dave

I think you would need a macro to do what you want. You would also need a unique identifier for each customer such as a customer ID. Can you post a screen shot of what your data on both sheets looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Lookup" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. I removed the formulas because they are no longer needed. Make a selection in the drop down in D4. Then enter a new variable in column F and exit the cell. All data will automatically be updated.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D4,F7:F11")) Is Nothing Then Exit Sub
    Dim customer As Range, var As Range
    Select Case Target.Column
        Case Is = 4
            Set customer = Sheets("Database").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
            If Not customer Is Nothing Then
                Sheets("Database").Range("B" & customer.Row & ":F" & customer.Row).Copy
                Range("D7").PasteSpecial Transpose:=True
                Application.CutCopyMode = False
            End If
        Case Is = 6
            Set customer = Sheets("Database").Range("A:A").Find(Range("D4").Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not customer Is Nothing Then
                Set var = Sheets("Database").Rows(1).Find(Range("B" & Target.Row).Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not var Is Nothing Then
                    Sheets("Database").Cells(customer.Row, var.Column) = Target
                End If
            End If
    End Select
End Sub

 

[Charmwah]

Hi Mumps

That is superb, and works instantly! Thank you so much for your help!

Is it at all possible to show some sort of warning before making the changes at all? I only ask as, since the spreadsheet is intended to be used by users of all proficiency levels i'd like to add in a failsafe of some sort. Something like a:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub AreYouSure()

Dim Sure As Integer

Sure = MsgBox("Are you sure?", vbOKCancel)
If Sure = 1 Then Call DeleteProcess

End Sub</code>

 

[mumps]

Do you want to to show the warning only for modifying the result with the values in column F? Also, in your response you said "Then Call DeleteProcess". This seems to indicate that you have another macro named "DeleteProcess" that you want to run. Is this the case?

 

[Charmwah]

Hi Mumps

Yes, the warning need only be for when you want to 'action' changing the new values entered into column F to transpose to the database sheet. This only needs to happen at the end, once all cells with revisions have text entered.

Excuse the 'Deleteprocess', I copied the code from an old spreadsheet. Though i'll probably add a line or two to clear the column F cells once the data has been transposed.

Thanks again!

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D4,F7:F11")) Is Nothing Then Exit Sub
    Dim customer As Range, var As Range
    Select Case Target.Column
        Case Is = 4
            Set customer = Sheets("Database").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
            If Not customer Is Nothing Then
                Sheets("Database").Range("B" & customer.Row & ":F" & customer.Row).Copy
                Range("D7").PasteSpecial Transpose:=True
                Application.CutCopyMode = False
            End If
        Case Is = 6
            If MsgBox("Are you sure you want to change the variable in cell " & Cells(Target.Row, 4).Address(0, 0) & "?", vbYesNo) = vbYes Then
                Set customer = Sheets("Database").Range("A:A").Find(Range("D4").Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not customer Is Nothing Then
                    Set var = Sheets("Database").Rows(1).Find(Range("B" & Target.Row).Value, LookIn:=xlValues, lookat:=xlWhole)
                    If Not var Is Nothing Then
                        Sheets("Database").Cells(customer.Row, var.Column) = Target
                    End If
                Else
                    Exit Sub
                End If
            End If
    End Select
End Sub

 

[Charmwah]

Hi Mumps

Thats excellent! I really appreciate your help! I have a feeling this may lead to some further questions down the line. Do you mind if I message you with them at all?

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D4,F7:F11")) Is Nothing Then Exit Sub
    Dim customer As Range, var As Range
    Select Case Target.Column
        Case Is = 4
            Set customer = Sheets("Database").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
            If Not customer Is Nothing Then
                Sheets("Database").Range("B" & customer.Row & ":F" & customer.Row).Copy
                Range("D7").PasteSpecial Transpose:=True
                Application.CutCopyMode = False
            End If
        Case Is = 6
            If MsgBox("Are you sure you want to change the variable in cell " & Cells(Target.Row, 4).Address(0, 0) & "?", vbYesNo) = vbYes Then
                Set customer = Sheets("Database").Range("A:A").Find(Range("D4").Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not customer Is Nothing Then
                    Set var = Sheets("Database").Rows(1).Find(Range("B" & Target.Row).Value, LookIn:=xlValues, lookat:=xlWhole)
                    If Not var Is Nothing Then
                        Sheets("Database").Cells(customer.Row, var.Column) = Target
                    End If
                Else
                    Exit Sub
                End If
            End If
    End Select
End Sub

 

[mumps]

You are very welcome. If the questions are regarding this same topic, then just respond in this thread.