Dependant Tables

Good morning all,

I'm new to this board having never joined one before and I need your help please!

I'm a bit of a novice when it comes to excel but I'm a quick learner and understand the workings of formulas and how to join several of them together.

I have a problem with the dependant drop-down lists function. I need to create one whereby the second drop-down relies on the first. I have googled this pretty extensively and have come up with a way to do it, using a table on another worksheet. (This is a work spreadsheet, an is to be given to customers to fill in, so where the drop downs come from doesn't need to be seen by them. I will name it 'Do Not Use'.)

The problem is, is that those tables, the ones that populate the drop-down menus in the data validation box, all come from the same column in another worksheet.

I will tell you what I mean, using simple examples. (I cannot use real-life work examples unfortunately, due it it being for my job.)

In a worksheet, the customer fills in one column with various types of fruit and in the column next to it, the colour of the adjacent fruit.

In another worksheet, they then choose from a drop down, the colour of a fruit. Then they choose, from a dependant drop down, which fruit they would like. This depends obviously, on which colour they have just chosen in the first drop-down.

My question is, how do I extract all of the green fruit into a column in the worksheet 'Do not use' and all of the yellow fruits into another column, the red in another etc etc, all in the same worksheet.

These columns would then be used to populate the second drop-down menu.

Ergo, I basically need a way for Excel to look at the 'Colour' column and extract the relevant values from the 'Fruit' column. (I've used an IF statement but this pulls them out into the relevant columns in the hidden worksheet but leaves them in the same row number as they were in the original column. If you could tell me a way to consolidate these, and get rid of the #N/A's too, that would be kind of perfect!)

I really need a fix for this as it's driving me and my boss nuts and has been for a few weeks now. (We've been working on it on and off)


Many thanks in advance for your help,

Jack

If you haven't already done so, look here:

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


Post a sample Excel workbook (not image) using the example you described in your post, showing expected outcomes.

To upload a file click "Go advanced" (next to "Reply") then scroll down to "Manage Attachments".

Hi John, thanks for your speedy reply.

I have already tried that link, and it helped me learn how to set up a dependant table. The problem I now have is that the source tables for the secondary drop-downs all come from the same column.

I need a way of splitting that column and moving into separate columns within a worksheet, depending on what their primary selection is.

I have attached a very simple and basic version of what I am after. Creating the dependant drop-downs is not the issue. It is how to populate the target tables for the lists, as explained in the spreadsheet called 'Lists!' In a comment in cell B1 (copied below also).

"This is how I want this table to look.

The information will be extracted from the table within the 'Fruits!' spreadsheet

I do not want to have to type it in manually here. I need to have it so that the columns in this worksheet are automatically updated, depending on what is typed into the table in 'Fruits!'

It needs to have no duplicates, and ideally no blank spaces at the bottom of each drop-down on 'Drop Downs!' "

Thanks again

See the attached file:

The lists of fruits are created dynamically using the following formula:

in B2 of "Lists"

=IFERROR(INDEX(Fruits!$B$2:$B$14,SMALL(IF(Fruits!$A$2:$A$14=B$1,ROW($A$2:$A$14)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

This must be entered using Ctrl+Shift+Enter

Copy across and down. Change the range (14) to whatever you require

The lists will grow/shrink as data is added/removed from table in Fruits.

For the drop-downs we have to create two named ranges for each colour:

Each colour is a named range of one cell ("Green" , "Red" etc) and the corresponding column is named "GreenCol", "RedCol" etc

The INDIRECT for the FRUIT drop down is now:

=OFFSET(INDIRECT($B2),1,0,COUNTIFS(INDIRECT($B2 &"Col"),"?*")-1,1)

See http://www.contextures.com/xlDataVal02.html for explanation (new to me!)

The COUNTIFS is used to ensure no blanks in the drop down list.

Another option based on "searchable" drop downs.

See these YouTube videos for an explanation:


https://www.youtube.com/watch?v=vkPoViUhkxU

https://www.youtube.com/watch?v=0QrQT9D25Xk

In tab "Fruits"

in d2 : =Cell("Contents")

This "captures" the last entry typed in any cell. When the "Fruit Colur" is selected it will populate D2

In A2 down

=IF($B2=$D$2,MAX($A$1:A1)+1,0)

Enter with Ctrl+Shift+Enter and copy down

in E2: named range "Search_List"

=IFERROR(VLOOKUP(ROWS($E$2:E2),$A$2:$C$100,3,0),"")

Copy down

This is the "dynamic" list created based on selection in "Drop Downs"

in "Drop Downs"

DV is now source: =Search_List

If you amend anything in "Fruits" you may get error messages which normally can be ignored.

Once all is set up, you can hide sheet "Fruits"