Forum Discussion

mazfly470's avatar
mazfly470
Copper Contributor
May 23, 2024

Importing data from rows based off dropdown menu?

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

  • 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.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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.

Resources