Excel database modification

C

Charmwah

Board Regular
Joined
Jan 23, 2017
Messages
64
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!
 
Sort by date Sort by votes
Hi Mumps

Thanks, yes it is. Progressing from your example, i'd like to be able to use an INDEX & MATCH formula to retrieve the initial data. This needs to be using multiple criteria. I've uploaded the example file again to the dropbox link below:

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

I can't seem to get the INDEX & MATCH formula to work, I suspect it may be to do with how my data is arranged on the database?

Kind regards

mumps said:
You are very welcome. :) If the questions are regarding this same topic, then just respond in this thread.
Click to expand...
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I assume that the 1st Example in "Lookup" is working properly. Just a reminder that the formulas you have in D7:D11 are not necessary as the macro will populate that range when you select a customer in D4. Unfortunately, formulas are not my strong suit. Could you explain in words what you are trying to do in the 2nd Example? Will you be using both examples in your workbook or just one of them?
 
Upvote 0
Hi Mumps

The current database I have requires updating manually by scrolling through the many different columns and then ensuring you are changing the right information for the particular customer. So, the idea behind example 2 is to choose the customer from a drop down list, then to choose the product from a 2nd drop down list, and for this to then populate a standard set of variables with the specific values for that customer and product. The user can then update those values using the earlier VBA script you detailed for example 1.

Kind regards
 
Upvote 0
Try this macro. In 2nd example, first select the customer in N3 then enter the Product in N4 and exit the cell.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D4,F7:F11,N4")) 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").Range("B2:F2").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
        Case Is = 14
            Set customer = Sheets("Database").Range("A:A").Find(Target.Offset(-1, 0), LookIn:=xlValues, lookat:=xlWhole)
            If Not customer Is Nothing Then
                Sheets("Database").Range("G" & customer.Row & ":K" & customer.Row).Copy
                Range("N7").PasteSpecial Transpose:=True
                Application.CutCopyMode = False
            End If
    End Select
End Sub
 
Last edited:
Upvote 0
Hi Mumps

That works, but it then returns the variables for both AA & BB, so 10 in all. I only need it to return the variables for either AA or BB, depending on which is chosen by the user. So the Range is B:K, but depending on the choice of AA or BB its either B:F or G:K.

I've tried to combine your two codes into one to make a more realistic example:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D4,F7:F11,N4")) 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.Offset(-1, 0), 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").Range("B2:F2").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


Does that make sense?

mumps said:
Try this macro. In 2nd example, first select the customer in N3 then enter the Product in N4 and exit the cell.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D4,F7:F11,N4")) 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").Range("B2:F2").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
        Case Is = 14
            Set customer = Sheets("Database").Range("A:A").Find(Target.Offset(-1, 0), LookIn:=xlValues, lookat:=xlWhole)
            If Not customer Is Nothing Then
                Sheets("Database").Range("G" & customer.Row & ":K" & customer.Row).Copy
                Range("N7").PasteSpecial Transpose:=True
                Application.CutCopyMode = False
            End If
    End Select
End Sub
Click to expand...
 
Last edited:
Upvote 0
Try:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D4,F7:F11,N4")) 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").Range("B2:F2").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
        Case Is = 14
            Set customer = Sheets("Database").Range("A:A").Find(Target.Offset(-1, 0), LookIn:=xlValues, lookat:=xlWhole)
            If Not customer Is Nothing Then
                If Target = "AA" Then
                    Sheets("Database").Range("B" & customer.Row & ":F" & customer.Row).Copy
                    Range("N7").PasteSpecial Transpose:=True
                    Application.CutCopyMode = False
                Else
                    Sheets("Database").Range("G" & customer.Row & ":K" & customer.Row).Copy
                    Range("N7").PasteSpecial Transpose:=True
                    Application.CutCopyMode = False
                End If
            End If
    End Select
End Sub
 
Upvote 0
Thanks Mumps

The code below now works for populating the variable fields, but the code to make the changes to the database no longer works, I think because the its only using a single denominator. Please note, I have removed example2, so everything is now taking place with the example 1 highlighted box:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D4,F7:F11,N4")) 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.Offset(-1, 0), LookIn:=xlValues, lookat:=xlWhole)
            If Not customer Is Nothing Then
            If Target = "AA" Then
                Sheets("Database").Range("B" & customer.Row & ":F" & customer.Row).Copy
                Range("D7").PasteSpecial Transpose:=True
                Application.CutCopyMode = False
            Else
                Sheets("Database").Range("G" & customer.Row & ":K" & customer.Row).Copy
                Range("D7").PasteSpecial Transpose:=True
                Application.CutCopyMode = False
            End If
        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").Range("B2:F2").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 said:
Try:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D4,F7:F11,N4")) 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").Range("B2:F2").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
        Case Is = 14
            Set customer = Sheets("Database").Range("A:A").Find(Target.Offset(-1, 0), LookIn:=xlValues, lookat:=xlWhole)
            If Not customer Is Nothing Then
                If Target = "AA" Then
                    Sheets("Database").Range("B" & customer.Row & ":F" & customer.Row).Copy
                    Range("N7").PasteSpecial Transpose:=True
                    Application.CutCopyMode = False
                Else
                    Sheets("Database").Range("G" & customer.Row & ":K" & customer.Row).Copy
                    Range("N7").PasteSpecial Transpose:=True
                    Application.CutCopyMode = False
                End If
            End If
    End Select
End Sub
Click to expand...
 
Upvote 0
Click here to download your file. Everything seems to be working properly including the code to make the changes to the database.
 
Upvote 0
Hi Mumps

Thanks for this. I think I may have confused you. My intention was to combine the two examples: Choose a customer from a drop down list, then choose a product; current variables then populate in one column and any changes to these variables can be entered in an adjacent column, with a warning prior to changing the value.

Both your examples work on the spreadsheet you sent me, but as separate examples, not a combined version.

Again, I really appreciate your time and assistance on this, you're a gentleman!
 
Upvote 0
Thank you for the kind words. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

afrazier1221
  • Solved
VBA adding symbols, disabling the intended code. Please help.
Replies
1
Views
473
afrazier1221
afrazier1221
S
  • Question
VBA Custom function to replicate INDEX MATCH MATCH with predefined fixed arrays
Replies
3
Views
1K
DanteAmor
DanteAmor
Jyggalag
  • Locked
  • Question
Please help me wrap up my IndexMatch formula (90% done)
Replies
1
Views
771
Fluff
Fluff
dreen
  • Question
Increasing the speed of Index & Match in VBA
2
Replies
13
Views
5K
DanteAmor
DanteAmor
Jyggalag
  • Question
Iferror(Index&Match&Match) formula
Replies
0
Views
663
Jyggalag
Jyggalag

Forum statistics

Threads
1,221,418
Messages
6,159,790
Members
451,589
Latest member
Harold14

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top