Data Valadation : If List A then List 1, If List B then List 2. If list 1, autocomplete

Hi guys!

Really struggling with this one as far outside my excel experience.

I'm trying to do a few things here, the first (I hope) is quite simple.

Problem A
In one cell I have a list which is just two options you can select, say either 'A' or 'B'. In another cell (based on info drawn from another sheet)I have two lists of about 30 options, with the whole lists named, say list '1' and list '2'.

What I would like is for the first list selection to determine what options are available in the second list selection. e.g, if A is selected from the list in first cell, the second cell gives the 30 options of list 1, alternateively if B is selected in first cell, then all the options for list 2 are named in the second cell. No idea what to do though, contemplated IF function but can't get it to work!

Problem B

Second part relates to this but might be more tricky. In lists 1 & 2, the format is something like this (content isnt important for this purpose):

List 1:

Cell A	Cell B	Cell C	Cell D
Jon	Cats	Mushrooms	Tarmac
Phil	Hairnets	Jam	Keyrings
Barry	Dogs	Fish	Table

This sort of format continues for about 30 lines for each list and is the second sheet on my workbook.

What I would like is if 'Jon' is selected from List 1 on my first sheet, it will automatically copy the information from the adjacent cells (i.e. cell B, C, D) in my second sheet into certain cells in my first sheet. Make sense? Probably not!

If you can help with either of these queries it would be much appreciated or if you need more clarity I'll do my best haha.

Many thanks

Rick

First problem can be dealt by dependent data validation. See link below

http://www.contextures.com/xlDataVal02.html

Second problem will need VBA

Sounds like you are talking about dependent drop downs

http://www.contextures.com/xlDataVal02.html

In the attached sheet, I named List 1 "A" and List 2 "B" as per you.
As per the above site, I put simple A,B data validation into A2 and in B2
=INDIRECT(A2)
To get the proper VLOOKUP to work, I created another Defined Name VLOOK

Formula:

Please Login or Register  to view this content.

Then in C2
=VLOOKUP(B2, VLOOK, 2, FALSE)
In D2
=VLOOKUP(B2, VLOOK, 3, FALSE)
Is that what you were looking for?