VBA & Macro Problems

How to Automatically Fill Dependent Excel Data Validation Values

Khadija Khan Oct 22, 2026 868 views

Question details

The user wants to automatically populate or update a dependent data validation drop-down cell when the value in the primary controlling cell is changed.

How to Automatically Fill Dependent Excel Data Validation Values

Product: Excel
Device & OS: not provided
Scenario: Creating an interactive spreadsheet with cascading drop-down lists where changing a main category automatically pushes a new value to the sub-category cell.
Observed behavior: Standard worksheet formulas only return values to their own cells and cannot directly change or push values into the dependent data validation cell.

Before you start

Before proceeding, ensure that the Developer tab is enabled in your spreadsheet software, as this solution requires adding a VBA macro to your workbook.

Solution 1Recommended

Use VBA Worksheet_Change Event to Auto-Fill Cells

Since standard worksheet formulas cannot push values into other cells, you must use a VBA macro to automatically update the dependent drop-down cell.

A foundational rule of spreadsheet formulas is that they can only return a value to the cell they reside in; they cannot alter the content, formatting, or data validation of another cell. To achieve automatic updates for dependent drop-down lists, a VBA event handler is required.

By utilizing the Worksheet_Change event, you can instruct the spreadsheet to monitor the primary drop-down cell. When a change is detected, the VBA script can automatically clear or fill the dependent cell's value based on the new selection.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Access Worksheet Code

In the Project Explorer panel on the left, double-click the specific worksheet name where your drop-down lists are located to open its code module.

Insert the Worksheet_Change Macro

Add a Private Sub Worksheet_Change(ByVal Target As Range) script. Configure the script to check if the 'Target.Address' matches your primary drop-down cell.

Define the Action for the Dependent Cell

Within the script, write the code to clear the dependent cell (e.g., Range("B2").Value = "") or set its value automatically when the primary cell changes.

Use VBA Worksheet_Change Event to Auto-Fill Cells

Save as Macro-Enabled: After testing your code, always remember to save your file as a Macro-Enabled Workbook (.xlsm) so the automated drop-downs function correctly upon reopening.

Powerful Spreadsheet Capabilities

Create and Run VBA Macros Easily in WPS Office

WPS Spreadsheets provides robust support for VBA macros, allowing you to easily automate tasks like cascading drop-down lists and dependent data validation values without dealing with complex compatibility issues.

1. Download WPS Office: Install WPS Office for free and open your spreadsheet file.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'VB Editor' to launch the macro environment.
3. Insert Your VBA Script: Paste your Worksheet_Change VBA code into the relevant sheet module to enable automatic updates for your dependent data validation values.
4. Save and Execute: Save the document in a macro-enabled format (.xlsm) to keep your automated drop-downs functioning seamlessly.

Full compatibility with Microsoft Excel VBA macros and .xlsm formats.Advanced Data Validation features to create complex and dynamic drop-down lists.Free, lightweight software with a familiar ribbon interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I auto-fill a dependent drop-down without using VBA?

No, standard formulas cannot push a value into another cell. While you can use the INDIRECT function to populate the drop-down list's options dynamically, forcing the cell to actually select or display a new value automatically requires VBA.

Why does my dependent drop-down keep the old value when I change the main category?

By default, data validation does not clear or reset the dependent cell when the parent cell changes. To prevent data mismatches, a simple VBA script is needed to clear the dependent cell whenever the main category is modified.

How do I enable the Developer tab to use macros?

In your spreadsheet application, go to File > Options > Customize Ribbon, and check the box next to 'Developer'. This will add the Developer tab to your menu, giving you access to the VBA Editor and macro tools.

Excel & Data Problems