Creating Multi-level Dependent Data Validation in Excel: A Comprehensive Guide

“`html

Creating Multi-level Dependent Data Validation in Excel

Creating Multi-level Dependent Data Validation in Excel: A Comprehensive Guide

Person typing on laptop

Are you struggling to create a multi-level, dependent data validation in Excel? You’re not alone. Many users find this feature challenging but incredibly powerful once mastered.

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

The Problem with Multi-Level Dependent Data Validation

Multi-level dependent data validation allows you to create dynamic dropdown lists that change based on the selection in another cell. This is particularly useful for creating forms, reports, or any scenario where choices are interconnected.

Why it happens:

The complexity of setting up named ranges and dependent lists
Understanding how to use the INDIRECT function effectively
Managing multiple levels of dependencies without errors

A Step-by-Step Solution: Creating Multi-Level Dependent Data Validation in Excel

Step 1: Prepare Your Data Structure

The first step is to organize your data into a structured format. For example, if you’re creating a form for selecting products and categories:

Create two separate tables:
- A table listing all product categories (e.g., Electronics, Clothing)</
- A table listing individual products within each category

Spreadsheet closeup with numbers

Step 2: Create Named Ranges for Each Table

Named ranges make it easier to reference specific data sets in your formulas.

Select the range containing product categories and name this range “Categories”
For each category, create a named range that includes all products within that category (e.g., Electronics_Products)

Step 3: Set Up Data Validation for Categories

Now you’ll set up the first level of data validation to select categories.

Select cell B2 where users will choose a category (e.g., Electronics)
Go to “Data” > “Data Validation”
In the Settings tab, set Allow: List and Source: =Categories

Step 4: Create Dynamic Dropdown for Products Using INDIRECT Function

The key to multi-level dependent data validation is using the INDIRECT function.

In cell C2 (where users will choose a product), enter this formula:

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Select cell C2 where users will choose a product (e.g., Laptop)

“`html

=INDIRECT($B$2 & "_Products")

“`

The above formula dynamically references the named range corresponding to the selected category.

Step 5: Set Up Data Validation for Products

Share this:
Click to share on Twitter (Opens in new window)
Click to share on Facebook (Opens in new window)

XYZ Mesh

XYZ Mesh is used to convert X Y Z data points into an Excel Formatted MESH configuration for the use of 3-D Surface Plots, Wireframe Charts, Contour Graphs and Scatter Plots.
Excel Draw

Excel Draw can create, view and export CAD style DXF drawing files using only Microsoft Excel. The best part; anyone with Excel can view the drawing! No CAD required!
G-Drive Linker

G-Drive Linker creates direct download links from Google Drive files, folders, documents, spreadsheets, presentations and slides. It also creates file tree links to be exported!
DXF Reader GT

DXF Reader GT is the quickest way to convert, or extract, CAD style DXF drawings into a list of data points and objects. Gather your data from DXF, no CAD required!
PDF GT

A Multi Purpose PDF application built for small businesses and home users. PDF GT was built to focus on Page Management, Combining/Creating/Separating, and OCR.

Gray Technical, PDF GT, DXF Reader GT, License Management Solutions, G-Drive Linker, Cel Tools, Excel Draw, Cuix Tools, TEXT to PPT, Screen Cap, Emailer, Point File Convert, DWG Browser, Slide Manager, XYZ Mesh, XL Chart, Rig Steer, Log Evolve, Well Foresight, Squirrel Eyes, Geo Series and Trilobite are all licensed Trademarks of Gray Technical, LLC. Excel, Word, Access and Outlook are all licensed Trademarks of Microsoft. AutoCAD is a licensed trademark of Autodesk, Inc. Google and Google Drive are licensed Trademarks of Google, LLC. Gray Technical is not affiliated with Microsoft, Google or Autodesk, Inc or any of their affiliates. Gray Technical is not responsible for any ads that may appear on its website, linked pages, or affiliated websites. Gray Technical reserves the right to change any information displayed, text, media, code, design or display on its website, software, designs or products with or without notice. Gray Technical reserves the right to deny trials and deactivate licenses with or without reason. Gray Technical reserves the right to deny refunds with or without reason. Gray Technical offers free customer support for Gray Technical products. Gray Technical does not offer customer support on software outside of Gray Technical products.

Gray Technical, LLC - Copyright 2015-2021