Dependent drop down in excel table 2 conditions

**faodavid** · 03-11-2016, 06:52 AM

Hi
Can anyone advise how I add a drop list list based on two conditions (see attached).

There are two companies ALPHA & BETA, Mark & Phil work for both companies representing a different roster of artists for each as below. I want to create a booking table that has the following column drop downs which AGENCY / AGENT / ARTIST BOOKED, In the artist booked drop down this must pick up which company and which agent then give a dropdown of the roster they represent for that company only - its easier to see on the attached. Any help would be greatly appreciated

http://www.excelforum.com/attachment...1&d=1457693427

AGENTS
COMPANY PHIL MARK
ALPHA THE BEATLES HAWKWIND
ALPHA THE ROLLING STONES LED ZEP
ALPHA THE WHO THE BEACH BOYS
BETA BRYAN FERRY ADELE
BETA LEO SAYER BARRY MANILOW
BETA STEVIE WONDER DAVID BOWIE

**udprocks** · 03-11-2016, 07:05 AM

HI,

please see the attached file, hope it will help you.

have created name range for MARK & PHIL you can see those on name manager using "CTRL+F3"

after that in H3 have applied data validation with indirect function.

**faodavid** · 03-11-2016, 07:35 AM

Hi

Thanks for getting back, Ive taken a look but seems to be a problem in that if you click the drop down against each of the agent names it gives the rosters for both companies rather than just the one listed in the table ieH 11 as an example the drop down should only contain brian ferry , leo sayer and stevie wonder which are the artist Phil represents for BETA?

Any ideas?

Thanks
David

**ChemistB** · 03-11-2016, 10:11 AM

With the active cell as H3, go to the Name Manager (on Formula Tab) > New
Artist = =INDEX(Sheet1!$B$3:$C$8, MATCH(Sheet1!$F3,Sheet1!$A$3:$A$8,0),MATCH(Sheet1!$G3,Sheet1!$B$2:$C$2,0)):INDEX(Sheet1!$B$3:$C$8, MATCH(Sheet1!$F3,Sheet1!$A$3:$A$8),MATCH(Sheet1!$G3,Sheet1!$B$2:$C$2,0))
Okay, close Name Manager

Then Select H3:H14 and select Data Validation
List = Artist
Okay

**faodavid** · 03-11-2016, 12:41 PM

Hi
Sorry about this - I tried expanding the rosters and seem to have lost it in the formulas, could you possibly advise on the revised attachment

**ChemistB** · 03-11-2016, 12:53 PM

Ahhh, the Agencies in Column A need to be sorted in Alphabetical order. See if that fixes things.

**faodavid** · 03-11-2016, 01:02 PM

Brilliant thanks

**faodavid** · 03-15-2016, 08:00 AM

Hi
Sorry about this when I try to put the formula into the actual sheet I’m using it shows error and I’m not sure which part of the formula I have got wrong - can you help – I’ve attached the actual document. To recap I’m trying to get a drop down box in column D (Artist) on Contract DB tab, the drop down should only show the artists represented by the agent and the right agency i.e. Selective (SA) David or Gav and TSTHS Agency David or Gav. I have out the list of agencies, artists and appropriate agent in the validation tab. Can I also ask if artists are added going forward to either agency will the formula pick them up?

Hope you can help

Many thanks in advance

Dave

**ChemistB** · 03-15-2016, 11:23 AM

I used the defined name Artist1 (since you already have Artist)

With active cell 'Contract DB'!D2
=INDEX(VALIDATION!$B$3:$C$35, MATCH('CONTRACT DB'!$B2,VALIDATION!$A$3:$A$35,0),MATCH('CONTRACT DB'!$C2,VALIDATION!$B$2:$C$2,0)):INDEX(VALIDATION!$B$3:$C$35, MATCH('CONTRACT DB'!$B2,VALIDATION!$A$3:$A$35),MATCH('CONTRACT DB'!$C2,VALIDATION!$B$2:$C$2,0))

**faodavid** · 03-15-2016, 12:37 PM

Hi
Thanks for this, though Im not sure if its picking up all

row 1 drop down is showing just one artist when should be showing 5
row 2 drop down looks fine
row 3 drop down is showing one when it should be showing 3
row 4 drop down looks fine

any ideas?

**ChemistB** · 03-15-2016, 12:52 PM

Where are you pulling values from? The table in Sheet Validation does not have 5 artists from Agency SA. in fact it had none until I changed the first one.

**JohnTopley** · 03-15-2016, 12:53 PM

In the last file from ChemistB there is only one artist for agency SA hence rows 1 and 3 are correct UNLESS you have added additional artists for SA.

Or the additions to SA are not the top of the Validation list. (must be sorted by agency) I added a new SA in row 5 and the validation showed 2 entries

**faodavid** · 03-15-2016, 12:57 PM

Sussed it - rows A4-7 on validation agency title was Selective rather than SA. Ive changed the agency Title to SA and it all picks up correctly. Thanks for all your help it very much appreciated

Dependent drop down in excel table 2 conditions

LinkBack

Thread Tools

Rate This Thread

Display

Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Re: Dependent drop down in excel table 2 conditions

Thread Information

Users Browsing this Thread

Similar Threads

How can I display information from a formatted table dependent on drop down menu selection

[SOLVED] I need help to combine and sum rows of data in excel dependent on 3 conditions

Dependent Drop Down Lists with Lookup in Table Formula

[SOLVED] Dependent Drop down list with DEPENDENT DEFAULT VALUE

[SOLVED] Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)

dependent drop down formula in excel 07

Excel dependent drop down forms?

Bookmarks

Bookmarks

Posting Permissions