Create data validation list from previous selection

mikehawk1

New Member
Joined
Nov 15, 2011
Messages
3
I have a large set of data that I need to use for data validation in drop down lists. The data is arranged in 4 columns and entries within each column may repeat numerous times. The four columns are as this..
Category Subcategory 1 Subcategory2 Subcategory3
The first data validation will come from the first column. Once this is chosen the second drop down should populate with the subcategory that correspond to that category stored in column 2. once that is chosen the thrid data validation should populate with the corresponding subcategory from column three etc.. This is a small sample of the data in the columns. Anyone point me in the right direction to accomplish this? Thanks


-- removed inline image ---
 
Last edited:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mikehawk1

New Member
Joined
Nov 15, 2011
Messages
3
VoG thank you for the quick reply. I see where your solution would work, but my problem is that the data set is very large around 15,000 rows and I would like to make it so that if changes are made in the data that the validation still picks up correctly. Naming all of these ranges would be very time consuming if I have to do that manually.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the forum,

YOu need to use offset methods.

If you note there are named ranges, in the validations there is a complex formula which uses the following:

=OFFSET(INDIRECT(SUBSTITUTE($F2," ","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($F2," ","")&"Col")),1)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>

Excel Workbook
ABCD
1FruitApplesCabbageBread
2Other StuffOrangesLettuceMeat
3VegetablesLemonsCarrotsChocolate
4GrapesBeets
5Brussels Sprouts
6Celery
Lists

Excel Workbook
BC
1CategoryItem
2Other StuffMeat
3Other Stuff
4Other Stuff
5
6
7
8
9
10
11
12
13
14
15
Data Entry
#VALUE!
 

mikehawk1

New Member
Joined
Nov 15, 2011
Messages
3
Thanks for the reply Trevor. That helped me out, and I was able to get the project finished.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Thats good to read and thank you for the feedback.;)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,414
Messages
5,596,001
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top