Data Validation Lists from Tables

Hi Cherrie,

Thank you for the forum question.


Short answer: a Data Validation list can’t directly pull from multiple tables at once. It expects a single contiguous range (or named range). But there are a few good workarounds depending on your setup.
________________________________________
Your options
Option 1 — Combine into one helper list (recommended)
Create a separate range/table that consolidates all values, then point Data Validation to that.
If you have Excel 365:
You can do this dynamically using formulas like:
=UNIQUE(VSTACK(Table1[Column], Table2[Column], Table3[Column]))
• VSTACK combines columns from multiple tables
• UNIQUE removes duplicates
• Then use that result range as your validation source
This is the cleanest and most flexible method.
________________________________________
Option 2 — Named Range with a formula
You can define a named range like:
=VSTACK(Table1[Column], Table2[Column])
``
Then use the name in your Data Validation:
=MyCombinedList
________________________________________
Option 3 — Manually combine tables
If you're not using dynamic array functions (older Excel):
• Copy all values into a single “master list” table
• Use that as your source
________________________________________
Option 4 — INDIRECT workaround (not ideal)
You can use INDIRECT, but:
• It doesn't combine lists
• It's mainly used to switch between lists, not merge them
So it won’t solve your “multiple tables at once” requirement.
________________________________________
Key limitation
Excel Data Validation:
• Accepts: a single range or array result
• Does NOT accept: multiple ranges like =Table1[Col],Table2[Col]
________________________________________
Best practice summary
If you want something dynamic and future-proof:
→ Use VSTACK + UNIQUE into a helper range or named range




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector