Function Problems

How to Create Autofill Text and Dependent Drop-Down Lists in Excel

Kushani Nimanthika Sep 30, 2026 869 views

Question details

The user needs a way to automatically output a checked/empty box or display a dependent drop-down list in a column based on a selection from a primary drop-down list.

How to Create Autofill Text or Dependent Drop-Down Lists in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Setting up an automated reporting sheet where a secondary cell reacts to a primary problem code selection, either by autofilling a symbol or providing a dynamically filtered drop-down list.
Observed behavior: The user wants to transition from a manual entry method to an automated conditional output or dependent data validation, but requires formulas compatible with both web and desktop environments.

Before you start

Ensure that your primary drop-down list is already populated. Note that while autofill text formulas work perfectly in Excel for the Web, dependent drop-down lists using Named Ranges are best created using the desktop version of Excel.

Solution 1Recommended

Use the IF Function to Automatically Output Checked or Empty Boxes

This solution uses a simple IF formula to instantly populate a cell with specific symbols or text based on the value selected in the primary drop-down list. It works flawlessly in both Excel for the Web and desktop versions.

Select the target cell

Click on the first cell in your target column (e.g., the REPORT RECEIVED column) where you want the checked or empty box to appear.

Enter the IF formula

Type the formula: =IF(A2="TRANSPORT A CITIZEN / D5","☑","☐") into the formula bar. Make sure to replace 'A2' with the cell containing your primary drop-down, and adjust the condition text to match your specific problem code.

Apply and fill down

Press Enter to apply the formula. Click on the cell again, grab the fill handle (the small square at the bottom-right corner), and drag it down to apply the formula to the rest of the column.

Use the IF Function to Automatically Output Checked or Empty Boxes

Customizing Output: You can replace the check box '☑' and empty box '☐' in the formula with any other text string, number, or symbol to suit your reporting needs.

Solution 2

Create Dependent Drop-Down Lists Using INDIRECT and Named Ranges

This method uses the INDIRECT function combined with Named Ranges to create a secondary drop-down list whose options change based on the primary selection. It is recommended for desktop Excel.

Efficient Data Management

Easily Create Dependent Drop-Downs in WPS Spreadsheet

WPS Spreadsheet offers robust, full-featured support for the INDIRECT function, Data Validation, and Named Ranges, allowing you to easily set up automated conditional text and dependent drop-down lists exactly like desktop Excel.

1. Open your worksheet: Launch WPS Spreadsheet and open your existing workbook containing the primary drop-down lists.
2. Define your Named Ranges: Navigate to the Formulas tab, select 'Name Manager', and define the ranges for your dependent lists using underscores instead of spaces.
3. Access Data Validation: Select the cell for your dependent drop-down, go to the Data tab, and click on 'Validation'.
4. Set up the INDIRECT formula: Choose 'List' under the settings, enter your =INDIRECT(SUBSTITUTE(A2," ","_")) formula in the source box, and click OK to finish.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls) and formulas.Comprehensive support for Named Manager and Data Validation features without web-based limitations.Lightweight, fast, and free to download for everyday data processing.Intuitive interface that makes setting up complex conditional logic simple.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my dependent drop-down list work properly in Excel for the Web?

Excel for the Web has limitations regarding the creation and management of Named Ranges, which are strictly required for the INDIRECT function to link multiple lists. It is highly recommended to use the desktop version of Excel or an alternative like WPS Spreadsheet to set these up.

What is the purpose of the SUBSTITUTE function in the dependent list formula?

Excel Named Ranges do not allow spaces. By using the SUBSTITUTE(A2," ","_") function, you are instructing Excel to automatically replace any spaces from your primary drop-down selection with underscores, ensuring it correctly matches the valid Named Range you created.

Can I use multiple IF conditions if I have different symbols for different problem codes?

Yes, you can use a nested IF statement or the IFS function to evaluate multiple conditions. For example: =IFS(A2="Code1","☑", A2="Code2","☐", A2="Code3","⚠").

Excel & Data Problems