Function Problems

How to Use a Dynamic Excel Drop-Down to Pull Data from Another Sheet

WPS Content Manager Oct 10, 2026 868 views

Question details

The user wants to pull and display financial data dynamically based on a company selected from a drop-down list.

How to Pull Data from Another Sheet Using a Dynamic Excel Drop-Down

Product: Microsoft Excel
Device & OS: not provided
Scenario: Building a financial statement template or dashboard where selecting a company automatically updates the displayed metrics.
Observed behavior: Needs to configure Data Validation and apply formulas like VLOOKUP or SUMIF to automatically fetch data from a secondary sheet.

Before you start

Ensure your source data on the separate sheet is organized in a clear, tabular format with column headers and no blank rows, which makes referencing much easier for lookup formulas.

Solution 1Recommended

Create the Named Range and Drop-Down List

Set up a Data Validation list using a named range to provide the selection menu on your dashboard sheet.

Using a named range for your drop-down list makes it easier to manage and reference data across different worksheets without complicated cell ranges.

Define a Named Range

Go to your source sheet, highlight the cells containing the unique company names, click into the Name Box (located to the left of the formula bar), type a name like 'Companies', and press Enter.

Access Data Validation

Navigate to the statement template or dashboard sheet. Select the cell where you want the drop-down to appear (e.g., cell A2), and go to the Data tab on the ribbon, then click Data Validation.

Configure the Drop-Down

In the Data Validation dialog box, under the Allow drop-down, select 'List'. In the Source box, type '=' followed by your named range (e.g., =Companies), and click OK.

Create the Named Range and Drop-Down List

Tip: If you format your source data as an Excel Table (Ctrl+T) before creating the named range, your drop-down list will automatically update whenever new companies are added.

Solution 2

Apply Lookup Formulas to Fetch Data

Use VLOOKUP, SUMIF, or XLOOKUP formulas to pull the corresponding financial data dynamically based on the current drop-down selection.

Seamless Spreadsheet Management

Create Dynamic Drop-Downs Easily with WPS Spreadsheet

WPS Spreadsheet offers full support for Data Validation, named ranges, and advanced lookup formulas like VLOOKUP and XLOOKUP, allowing you to build automated financial dashboards effortlessly.

1. Define your range: Open your workbook in WPS Spreadsheet, highlight your source list, and define a name in the Name Box.
2. Create the drop-down: Navigate to Data > Validation, choose 'List', and input your named range (e.g., =Companies) to create the drop-down menu.
3. Fetch the data dynamically: Use `=VLOOKUP()` or `=XLOOKUP()` in adjacent cells, referencing your drop-down cell to automatically pull in the related data.

100% compatible with Microsoft Excel formulas and data validationLightweight application that runs smoothly even with large datasetsFree, intuitive interface for seamless data analysis and reportingBuilt-in support for advanced functions including XLOOKUP and SUMIFS

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP return an #N/A error when selecting a drop-down item?

This usually happens if the selected item has trailing spaces or formatting differences compared to the source data. It can also occur if the lookup column (containing the company names) is not the first column in your specified VLOOKUP range.

How can I make the drop-down list update automatically when I add new companies?

Format your source list as a Table by pressing Ctrl+T before creating the named range. When you add new rows to the bottom of the table, the named range will automatically expand, instantly updating your drop-down list choices.

Can I pull data from another workbook instead of just another sheet?

Yes, you can reference another workbook in your VLOOKUP formula. However, both workbooks need to remain in their original file paths. It is highly recommended to keep the source workbook open while working to prevent '#REF!' or update errors.

What is the advantage of using XLOOKUP instead of VLOOKUP here?

XLOOKUP is more flexible because it can search data in any direction (not just left-to-right), doesn't require hardcoding column index numbers, and is much less likely to break if you insert or delete columns in your source sheet later on.

Excel & Data Problems