Validation list based upon result of another validation list

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Hi - don't know if this is possible but I've got 4 lists in columns A to D. I was intending to have a validation list (in E4) where the user chooses one of four headings (relating to columns A-D) - then depending on the heading chosen a second validation list (E5) displays the relevant list to that heading, e.g. choice 1 would be column A, choice 2 column B and so on.

Could anyone point me in the right direction please?

Cheers,
rrenis :biggrin:
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Good evening,

Let's say in a1 is my column header NAME
in b1 is my column header ADDRESS
in c1 is ......AGE
I will create a named range called HEADERS
Select a1:c1 and do inserT a name call it HEADERS.
(I suppose you know how to create a named range)

Then you will select the entire columns A TO C and do Menu
Insert...
Name...
Create...
Select only top row


In e1, you will have a data validation,
using Menu...
Data...
Validation....
List....
in Source... you will type
=headers

in f1 you will have a data validation same as above

List...
in source you will HAVE:
=INDIRECT(E1)

This is it

Meaning when you change e1, f1 will change accordingly.
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Hi sunnyland - thanks for the reply that's just what I'm after!! :biggrin:

Cheers,
rrenis
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Hi sunnyland - I get the following error when I enter =OFFSET(E1) in the validation list for F1...

"The Source currently evaluates to an error"

I'm sure I'm doing something wrong here but does anyone have any ideas what? :oops:

cheers,
rrenis
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Sorry - I meant to type INDIRECT :oops:

Thanks for the link onlyadrafter - I'll check it out! :biggrin:

cheers,
rrenis :)
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
From reading sunnylands posts and then the link posted by onlyadrafter this is definitely the way to acheive what I'm after but I still keep on getting the error when trying to insert INDIRECT in the Validation list. Has anyone come across the error I'm getting that could shed some light onto what I'm doing wrong? :oops:

cheers,
rrenis :)
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Hi - managed to get it working - the problem was that my headings were in the following format > 01 Text, 02 Text etc and these were displayed as _01_Text, _02_Text in the names and therefore didn't correspond to the selection made in the first validation list. Many thanks to sunnyland and onlyadrafter for your help.

By the way if anyone knows of a way to use a heading such as 01 Text etc I'd appreciate it :biggrin:

cheers,
rrenis :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,296
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top