Importing data from rows based off dropdown menu?

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.