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:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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!
 
Upvote 0
Thats good to read and thank you for the feedback.;)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top