Combining Dropdown list and Consolidate Function in Excel

Question

Hi MVP,

Trust you are well.

I have got this urgent and vital requirement that you might find very simple and straight forward. The requirement as follow;

I created a table in an excel spreadsheet for a form that will be filled by the users / colleague I intend to gather information from. So, I require 2 columns in the table of the form to have a dropdown list of data that users will pick / select appropriate item from the list.

Based on the aforementioned description of the need, please see below relevant parts of requirement I required solutions for;

Part A:

The dropdown list for the 2 columns to consist of parent and child data; for instance:

1.     Parent (Column A)

                      i.        Laptop A,

                     ii.        Laptop B

2.     Child (Column B)

                      i.        Laptop A – Headset

                     ii.        Laptop A – Mouse

                    iii.        Laptop A – Webcam

                    iv.        Laptop A – SD Memory

                     v.        Laptop B – Headset

                    vi.        Laptop B – Mouse

                   vii.        Laptop B – Webcam

                  viii.        Laptop B – SD Memory

Part B:

To define column for parent and child, create the dropdown list in both columns with parent data for parent column and child data for child column. But, the functionality needed is such that if the Laptop A in the parent column is selected, the list in child data should automatic filtered for Laptop A data set only for target user to pick from instead of having entire list and had to scroll through list not required and could be burdensome if dropdown list is in the hundreds.

So, the solution I require is to write formula that will work in such a way when selecting an item from the parent data and column will auto-filter for the list item in the child column aligned to item selected in the parent column.

I would greatly appreciate to receiving your solution to this challenge.

Thank you.

Kind Regards,

Olu

Answer 1

Hello,

you can use the Name Manager to create a range name that references a formula. On the Formulas ribbon, click "Name Manager". Click the "New" button. In the dialog that opens up, enter a name and in the "Refers to" box enter the formula.

When you enter the formula, you can click on cells in the workbook to add them to the formula. So, for example, if you want to write the formula ...

=Index(Sheet1!$A:$A,Match(Sheet2!$M$17,Sheet1!$B:$B,0))

... you start by typing a = sign and write Index(

Then click on Sheet1 and select column A

Then type a comma and type Match(

Then click on Sheet2 and select cell M17

then type a comma and click on Sheet1 and select column B

then type a comma and a zero and two closing brackets ))

The Name Manager can also be used to check what a dynamic range name currently refers to. Open the Name Manager, click on one of the name in the list, and the range will be highlighted with a marching ants border.

cheers, teylyn

Answer 2

Hello Olu,

what you describe is called "Dependent Data Validation" and there are several approaches to achieve this.

Here is a link to a file with one way of doing it:

https://skydrive.live.com/redir?resid=FE479525195477F7!320&authkey=!ALFLv-aMREYK1HA

The list of laptops is in cells A2 to A4. Cell H2 has Data Validation applied with the "List" option and the source of the list is A2:A4.

The dependent list is in columns C and D. Column C has the Laptop name for each component and column D has the actual components. IMPORTANT: The list of components must be sorted by the laptop names in column C, otherwise the solution will not work.

A range name called ComponentList has been defined with the following formula:

=INDEX(Sheet1!$D:$D,MATCH(Sheet1!$H$2,Sheet1!$C:$C,0)):INDEX(Sheet1!$D:$D,MATCH(Sheet1!$H$2,Sheet1!$C:$C,1))

This formula returns a list of components in column D where column C shows the same laptop name as in cell H2.

Cell H3 has data validation applied with the "List" option and the source is the range name

=ComponentList

Now, select a laptop in H2, then open the drop-down in H3 and you will see only the components for that laptop.

If you select a component and then change the laptop model in H2, we need to empty the cell in H3. This is done with a little macro. Right-click on the sheet tab and select "View Code". You will see this macro

Private Sub Worksheet_Change(ByVal Target As Range)

' clear cell H3 when cell H2 changes

If Not Intersect(Target, Range("H2")) Is Nothing Then

Range("H3").ClearContents

End If

End Sub

Since the workbook has a VBA macro, it must be saved as a macro enabled file with the extension xlsm.

Hope that helps,

cheers, teylyn

Answer 3

Assume your first list is X1,X2,X3 is in the range F2:F4, Give Heading of this List as X in F1

Same way 2nd list is Y1,Y2,Y3 in the range G2:G4, Given Heading of this list as Y in G1

Now Select range F2:F4 and give the Name of this range as 'X'

Same way select range G2:G4 and the Name of this range as 'Y'

Go to cell A1 and data validation here select LIST in 'Allow Tab' and in source give range F1:G1, ok,  and come out

Now go to cell A2 and data validation, here slect LIST in 'Allow Tab' and in source give formula as =indirect(A1)    ok, and come out.

Also refer the thread..

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/insert-condition-in-data-validation-list/d343922f-10a7-47ac-9df5-48734d8323a2

Answer 4

Hi Teylyn,

I will trial your solution and if it works for me I will mark it as the global answer.

Speak to you shortly.

Thank you.

Kind Regards,

Olu