[Solved] Make a list from a matrix

emflorence.br

New member
Hello Exceldemy team!
I need to build up a spreadsheet that display the quantity of the items that i have in my stock around 10 warehouses. For a brief example, lets say that I have lines with the warehouses and columns for the items. The matrix contains quantity of all of them.

So it would be a list spreadsheet like this:
Warehouse Water Coke D_Coke Bread Milk Macaroon Eggs Meat Truffles
A 0 0 1 0 1 0 5 1 0
B 2 1 0 0 0 0 0 1 2
C 5 0 1 0 1 0 1 1 4
D 1 0 9 2 1 4 2 3 0
E 3 0 1 1 1 0 0 1 6
F.... and it goes like this.

Than i need to crate a drop down menu with the warehouse I want to verify stock and I would like to display in a text box the quantity of the items that I have in that warehouse, for example:
Warehouse B: 2 Water; 1 Coke; 1 Meat; 2 Truffles.

Can you help me? Do I need a macro for that?
 
Last edited:
Hello emflorence.br,
Thanks for sharing your problem with us. I understand that you want to build up a spreadsheet that displays the quantity of the items that you have in your stock for the warehouse selected through a dropdown.

From your given data, I created the following dataset:

2tPi35uBK4m-wqXPBc2BEbb_Jelieqaa1hqlIf6ii4VG0YwG9id0scGqmWswo52As27NFCW_kD106tGoqurKdTEJF8HNM_iDoLmUL2OzsWK52hXFLapK7UmxcII1wpRNVN7RwSNrQ1AIe9Hz87ynfPU

To add a dropdown list in Cell B13, select the cell >> go to the Data tab >> click on the dropdown menu of Data Validation >> select the Data Validation option.

z7HEz_GbJD47TUXXV9UVxA-NZKtlbo4ErV5TBCI-gckf0kg82UEExHWz28nV3I3Ne5fPlF_pQCbPbAersWarA9FfQ7GEkmpfNZQuXQwQotkceYnKEAkgYxnUZqPiZI40q6I36I85sMAeC3m74ib7LsY

From the Data Validation user form, go to the Settings tab >> Set the Allow option to List >> Set the Source for the list to range A2:A11.

rjDCH4iuTrNg5FhZuWh7e7y46vSxoQqJyWFAHSe3NqCIMGXEl-gpadFUCLWT947Jh4F5EJ5faiHG97rcEYBg6YB3hBII2xHp4XjjJ8HfqcObEVGPbvAxrygq8SmLPmvwkMWv4ijBLJdUNjRYNWRaaI0

Next, right-click on the sheet name tab (i.e. Leaf Bar) and select the View Code option.

nG3qFe4PnnR3Hm5_vO4toQH87W485-mydoY6403MVYXDrga4e2H4WWu_5YYEpqkuBh6ck2t3gxUxw882o91O1iVOP8-2Lfl9YRyncjn9kAxdmrmBm9EUlUH1w2SReOI91ad9JeGLhxDA66LzkXdLP4c

Insert the following VBA code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Set ws = ThisWorkbook.Worksheets("Data")
    Set Rng = ws.Range("A1:J11")
    
    Dim i As Integer
    Dim j As Integer
    Dim outString As String
    
    If Target.Address = "$B$13" Then
        If ws.Range("B13") <> "" Then
            outString = "Warehouse " & ws.Range("B13") & ": "
            
            For i = 2 To Rng.Rows.Count
                If Rng.Cells(i, 1).Value = ws.Range("B13") Then
                    Exit For
                End If
            Next i
            
            For j = 2 To Rng.Columns.Count
                If Rng.Cells(i, j).Value <> 0 Then
                    outString = outString & Rng.Cells(i, j) & " " & Rng.Cells(1, j).Value & "; "
                End If
            Next j
            
            outString = Left(outString, Len(outString) - 2)
            
            MsgBox outString
        End If
    End If

End Sub

Now, go to the datasheet and select any value from the dropdown list in Cell B13. A Message Box with the required output will pop up.

KawCiBn-b5KFDCK8gGC4fv7yYVUuZDMj6pC8tf2-HDxbMbXojc2xLIQhGqPU07IQe5TAUFPLopwB2Z5hA1iUMcUKyggfFkvqT1GeBbvRdbP2oehgmhwiGOts2X5hf2oUd4VG980PtTHQz2J4EDrX9wg

If you change the warehouse value in Cell B13, you will get a different Message Box. However, if you set Cell B13 to empty or change any cell other than B13, there will not be any Message Box.

The workbook I used for demonstrating your problem is attached below. Hopefully, I was able to resolve your problem. Let us know your feedback.

Regards,
Seemanto Saha
ExcelDemy
 

Attachments

  • Make a list from a matrix.xlsm
    17 KB · Views: 2

Online statistics

Members online
1
Guests online
15
Total visitors
16

Forum statistics

Threads
311
Messages
1,378
Members
568
Latest member
WilliamHon
Top