สร้าง Drop-down List แบบ 2 ชั้น เปลี่ยนอัตโนมัติ เมื่อเลือกรายการ Excel

Introduction

This tutorial will guide you through the process of creating a two-level drop-down list in Excel that updates automatically based on your selections. Using the INDIRECT function, you can manage complex data more efficiently, making your data entry organized and straightforward. This is especially useful for anyone who needs to filter information easily, such as business analysts or data managers.

Step 1: Prepare Your Data

• Organize your data in a structured format:
  • Create a list of categories (e.g., brands) in one column.
  • Next to each category, list the corresponding items (e.g., models) in separate columns.
• Ensure that each category has its own distinct range of models.

Step 2: Define Named Ranges

• Highlight the range of models for each category.
• Go to the "Formulas" tab and click on "Define Name."
• Enter a name that matches the category (e.g., if your category is "Apple," name the range "Apple").
• Repeat this process for all categories.

Step 3: Create the First Drop-Down List

• Select the cell where you want the first drop-down list.
• Navigate to the "Data" tab and click on "Data Validation."
• In the Data Validation dialog:
  • Choose "List" from the "Allow" dropdown.
  • In the "Source" field, enter the range of categories (e.g., =A1:A5).
• Click "OK" to create your first drop-down list.

Step 4: Create the Second Drop-Down List

• Select the cell for the second drop-down list.
• Again, go to "Data Validation" under the "Data" tab.
• Choose "List" from the "Allow" dropdown.
• In the "Source" field, use the INDIRECT function:
  • Enter =INDIRECT(A1) where A1 is the cell of the first drop-down list.
• Click "OK" to finalize the second drop-down list.

Step 5: Test Your Drop-Down Lists

• Click on the first drop-down list and select a category.
• The second drop-down list should now display only the options corresponding to the selected category.
• Ensure that all selections are functioning as expected.

Common Pitfalls to Avoid

• Ensure that the named ranges exactly match the category names without spaces.
• Double-check the references in the INDIRECT function; it should reference the correct cell for the first drop-down list.
• Make sure there are no blank cells in the ranges used for the lists.

Practical Tips

• Use clear and concise names for your ranges to avoid confusion.
• Test your setup with different combinations to ensure the lists are dynamically updating as intended.
• Consider saving your workbook as a template if you plan to reuse this setup frequently.

Conclusion

You have successfully created a two-level dynamic drop-down list in Excel using the INDIRECT function. This method enhances data management and entry efficiency. For further exploration, consider looking into advanced Excel functions or other data validation techniques to expand your skill set. Happy Excel-ing!