Hands-On Lab: Excel INDIRECT for Dependent Dropdown Lists

Have you ever created a Province dropdown and wanted the City list to change automatically? 😱

In Excel, this can be done using a combination of Data Validation and the INDIRECT function.

This technique is commonly used for:

• Data entry forms
• Customer databases
• Province and city area systems
• Interactive dashboards
• Simple administration applications in Excel

🌟 Benefits of INDIRECT in Excel

• Create automatic dependent dropdown lists
  The second dropdown changes based on the first dropdown selection.
• Make forms look more professional
  Data becomes cleaner and easier to select.
• Reduce input errors
  Users only need to select from available data.
• Perfect for regional databases
  Very useful for Provinces, Cities, Districts, and more.

📘 Functions Used

• Data Validation → creates dropdown lists
• INDIRECT → reads range names dynamically

🧠 Practice Data Structure

In this practice, we will create:

• First dropdown → Provinsi
• Second dropdown → City/Regency

📍 Provinsi List

📍 Banten Data

📍 Jabar Data

🧭 Steps to Create Dependent Dropdown Lists

1. Create the Province list in one Excel column.
2. Create city lists for each province.
3. Select the Province data and create a named range: Provinsi
4. Select the Banten data and create a named range: Banten
5. Select the Jabar data and create a named range: Jabar
6. Create the first dropdown using Data Validation:

Next, create the second dropdown using the INDIRECT function:

If cell B3 contains: Banten, Excel will automatically retrieve data from the range named Banten 😱

📘 How INDIRECT Works

The INDIRECT function converts text into a range reference.

Example:

That is why the second dropdown can automatically change based on the user's selection.

🖼️ Practice Illustration

⚠️ Common Mistakes

• The range name is different from the dropdown value
• Spaces are used in range names
• Incorrect INDIRECT formula
• Wrong reference cell selected
• Named Range has not been created yet

🎥 Excel INDIRECT Video Tutorial

The video above demonstrates how to create automatic dependent dropdown lists using INDIRECT and Data Validation in Excel.