Importing data from rows based off dropdown menu?

[mazfly470]

I have been given a task to create an inventory sheet for my place of work.  For my inventory task, I have completed the inventory by building. I also need to create a separate inventory sheet by category as well. I was wondering if it would be possible to use the drop-down menu to automatically add a row to a new worksheet?

For instance, I have 10 scissors in the East Building. This is office supplies. Could I use a dropdown menu to designate this as office supplies and have the row with scissors be automatically added to another workbook designated to office supplies? The row also included info like cost, link to where to buy, etc. I am hoping that this is a possibility, and that if we update the original workbook to reflect a different number of scissors the office supply workbook would be updated as well. I am new to excel and am having difficulty finding which formulas would work best for this scenario

[#2 · Accepted solution]

@mazfly470 

You can achieve this by using Excel's built-in features such as data validation for the dropdown menu, along with a combination of formulas (like FILTER or VLOOKUP) and possibly some VBA for more advanced automation.

Here is a step-by-step guide on how you can set this up:

Step 1: Setting Up the Inventory Sheet with a Dropdown Menu

1. Create Your Inventory Sheet:
• List all your items with their details (e.g., Item Name, Location, Category, Quantity, Cost, Purchase Link, etc.).
2. Add a Dropdown Menu for Categories:
• Create a list of categories somewhere in your sheet or in a separate sheet.
• Select the cells in the "Category" column where you want to add the dropdown.
• Go to the Data tab, click Data Validation.
• In the Data Validation dialog box, set Allow to List and Source to the range containing your categories.

Step 2: Creating a New Worksheet for Each Category

1. Create a Worksheet for Each Category:
• Add a new worksheet for each category you want to track (e.g., Office Supplies, Cleaning Supplies, etc.).
2. Use the FILTER Function:
• In the category-specific worksheets, use the FILTER function to pull data from the main inventory sheet.
• Example formula for Office Supplies worksheet (assuming your inventory data is in a sheet named "Inventory" and the category is in column C):

=FILTER(Inventory!A:F, Inventory!C:C="Office Supplies")

• Adjust the ranges and the column letters to fit your actual data layout.

Step 3: Automating Updates with VBA (Optional)

For more advanced automation, especially if you want rows to be automatically added to a new worksheet as you update the dropdown, you might need to use VBA.

1. Open the VBA Editor:
• Press Alt + F11 to open the VBA editor.
• Insert a new module by clicking Insert > Module.
2. VBA Code for Automatic Updates:
• Below is an example of a VBA script that copies rows to the appropriate sheet based on the category selected in the dropdown. This script should be placed in the worksheet module of your inventory sheet (right-click the sheet tab, select View Code, and paste the code):

Vba Code is untested, please backup your file.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim srcSheet As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim category As String
    Dim destSheet As Worksheet
    
    Set srcSheet = ThisWorkbook.Sheets("Inventory") ' Change "Inventory" to your sheet name
    
    If Not Intersect(Target, srcSheet.Range("C:C")) Is Nothing Then
        Application.EnableEvents = False
        
        ' Define the range to copy (entire row)
        Set rng = srcSheet.Rows(Target.Row)
        
        ' Get the category
        category = Target.Value
        
        ' Set the destination sheet based on the category
        On Error Resume Next
        Set destSheet = ThisWorkbook.Sheets(category)
        On Error GoTo 0
        
        If Not destSheet Is Nothing Then
            ' Find the next empty row in the destination sheet
            lastRow = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row + 1
            
            ' Copy the range to the destination sheet
            rng.Copy
            destSheet.Rows(lastRow).PasteSpecial Paste:=xlPasteValues
            
            ' Clear the clipboard
            Application.CutCopyMode = False
        End If
        
        Application.EnableEvents = True
    End If
End Sub

• This code triggers every time a change is made in the "Category" column (assuming it’s column C). It copies the entire row to the sheet named after the selected category.

Step 4: Keeping Data Synced

To ensure updates in the main inventory sheet reflect in the category sheets:

• If you are using formulas like FILTER, updates will be automatic as long as the formulas are set up correctly.
• If using VBA, you might need to extend the script to handle updates and deletions, which can be more complex.

Conclusion

By combining data validation, formulas, and potentially VBA, you can create an automated and dynamic inventory management system that categorizes items based on dropdown selections and updates corresponding sheets accordingly. This approach helps in keeping your data organized and up-to-date across multiple sheets. The text, steps and vba code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.