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!

 

[Charmwah]

No problem.

Did you have any thoughts about combining the two functions at all please?

Kind regards

 

[mumps]

I'm not clear on what you mean by "combining the two functions". Will you be using either the 1st Example or the 2nd example or both? Please explain in detail using the "Lookup" sheet and referring to specific cells and ranges as to what you want to do.

 

[Charmwah]

Hi Mumps, i'm sorry for not making it clearer.

I need to use both functions combined. I need the user to be able to narrow down the 'product' that they need to view the variables for, by using the drop down lists on the Lookup sheet. Then, once they have found the product variables they want, they need to be able to revise the fields using the adjacent cells if necessary. This is how its shown in the 'Example 2' file available from this link: https://www.dropbox.com/s/7mnfte0b186uf2h/Example2.xlsm?dl=0

In reality, there may well be further series of dynamic drop down lists in order to get to the correct list of variants, but for now the example model should be instructive.

Thanks again for your time.

 

[mumps]

First move the data in column R of the Database sheet down one row. This is so the "AA" is not in the same row as the other product values. Then try this macro:

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, product As Range
    Dim x As Long
    Dim sAddr As String
    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
            Select Case Target.Value
                Case "AA"
                    Set product = Sheets("Database").Rows(1).Find(Target, LookIn:=xlValues, LookAt:=xlWhole)
                    If Not product Is Nothing Then
                        x = Sheets("Database").Rows(1).Find(What:=Target, After:=Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _
                            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
                        Sheets("Database").Range(Sheets("Database").Cells(customer.Row, product.Column), Sheets("Database").Cells(customer.Row, x)).Copy
                        Range("D7").PasteSpecial Transpose:=True
                        Application.CutCopyMode = False
                    End If
                Case "BB"
                    Set product = Sheets("Database").Rows(1).Find(Target, LookIn:=xlValues, LookAt:=xlWhole)
                    If Not product Is Nothing Then
                        x = Sheets("Database").Rows(1).Find(What:=Target, After:=Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _
                            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
                        Sheets("Database").Range(Sheets("Database").Cells(customer.Row, product.Column), Sheets("Database").Cells(customer.Row, x)).Copy
                        Range("D7").PasteSpecial Transpose:=True
                        Application.CutCopyMode = False
                    End If
                End Select
            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("D3"), LookIn:=xlValues, LookAt:=xlWhole)
                If Not customer Is Nothing Then
                    Set product = Sheets("Database").Rows(1).Find(Range("D4"), LookIn:=xlValues, LookAt:=xlWhole)
                    If Not product Is Nothing Then
                        sAddr = product.Address
                        Do
                            If product.Offset(1, 0) <> Cells(Target.Row, 2) Then
                                Set product = Sheets("Database").Rows(1).FindNext(product)
                            Else
                                Sheets("Database").Cells(customer.Row, product.Column) = Target
                                Range("D" & Target.Row) = Target
                                Exit Sub
                            End If
                        Loop While product.Address <> sAddr
                        sAddr = ""
                    End If
                End If
            Else
                Exit Sub
            End If
    End Select
End Sub

This macro will allow you to have any number of customers, variables and products.

 

[Charmwah]

Hi Mumps

That works perfectly! Am I right in thinking that if I need to add further products I can just add an extra 'case' within the VBA?

What happens to the code if say, i need to add a further level to help the customer identify the products. For example the drop down list flow might be 'Customer > Product > SubType = variables'

Kind regards

 

[mumps]

If you need to add further products, just follow the "Case" pattern and add as many as you need. If you want to add a further level, I would need to see a revised file with the additional drop down and a detailed explanation of what you want to happen.

 

[Charmwah]

Hi Mumps

Attached is a link with a 3rd example. It includes the extra level, so now the first drop down chooses the customer, the 2nd drop down chooses the type, and the 3rd chooses the product. As before, it then needs to return the values from the database tab, with an option to amend these in exactly the same way as before.

https://www.dropbox.com/s/eoswwngceusssqa/Example3.xlsm?dl=0

Once again, I really do appreciate your assistance with this. I wasn't at all sure at the beginning if any of this could be done, so you've really opened my eyes (and generated new questions along the way!).

Kind regards

Dave

 

[mumps]

Will there always be only 5 variables for each product?

 

[Charmwah]

Hi Mumps

Good question. In the majority of cases it will be only 4 for each 'Type'. However, there are some exceptions where there will be as many as 15.

Kind regards

 

[mumps]

After having a closer look at your data, I have to ask for some clarification. I'm not sure why the "Type" is important. If you select Product "CC" for example, it is automatically a Type2. The same applies for "DD". It seems that the "Type" is determined by the "Product". So if you select "AA" or "BB" the "Type is automatically "Type1". Would this be correct?