Using a drop down list to display a data set/table

**EJ101414** · 09-08-2014, 09:21 AM

Hello,

I am trying to use a drop down list (which I have already created) to display a data set/list of information. Initially, I thought I could use a pivot table with a filter (which is exactly the way I want the data displayed), however, I want a single drop down list to change 2 different tables on one spread sheet. For example, I have a Group list that I am using for the drop down selection (or pivot table filter). Each group has a list of people (different number of people in each list) that includes their name, age, and contact info. I want to select "Group A" and have all of Group A's information listed below (in a formatted table). However, I have additional data associated with "Group A" that I want displayed in another table on the same worksheet. And like I said before, I would like to have all the tables updated with one drop down list. I am not an excel pro, so there may be an easy solution to my problem. Does anyone have any suggestions?

ThankS!

**newdoverman** · 09-08-2014, 09:31 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**EJ101414** · 09-08-2014, 10:30 AM

Here is a basic example of what I would like to do. I want to be able to select by Group within the drop down list, and display the data in the table below. The data is listed on the Data tab of the worksheet. Thanks again for your help.

**newdoverman** · 09-08-2014, 04:17 PM

I think that this does what you want.

**newdoverman** · 09-08-2014, 04:34 PM

One note on the solution given you; if you move the forms you will have to change the reference in the rows() part of the formulae. All the formulae are ARRAY ENTERED (Ctrl + Shift + Enter)

These are the formulae

Entered in B8 and copied across and down:

Formula:

=IFERROR(INDEX(Data!B$2:B$14,SMALL(IF(Data!$A$2:$A$14=Form!$B$3,ROW(Data!$A$2:$A$14)-MIN(ROW(Data!$A$2:$A$14))+1),ROWS($1:1))),"")

Entered in I8 and copied down only:

Formula:

=IFERROR(INDEX(Data!B$2:B$14,SMALL(IF(Data!$A$2:$A$14=Form!$B$3,ROW(Data!$A$2:$A$14)-MIN(ROW(Data!$A$2:$A$14))+1),ROWS($1:1))),"")

Entered in J8 and copied across and down:

Formula:

=IFERROR(INDEX(Data!H$2:H$14,SMALL(IF(Data!$A$2:$A$14=Form!$B$3,ROW(Data!$A$2:$A$14)-MIN(ROW(Data!$A$2:$A$14))+1),ROWS($1:1))),"")

**EJ101414** · 09-08-2014, 04:44 PM

Thanks again for your time. I appreciate it. I am, however, encountering an error when I try to apply the solution to other tables. Any idea why?

**newdoverman** · 09-08-2014, 04:57 PM

Your picture is a counter. You will see it in all the formulae that I gave to you. If you are getting a #Ref error in the error that your picture shows, set the value to Rows($1:1). If you are getting a different error, I'd have to see what you are doing in order to diagnose it.

Also, make sure that you are entering the correct formula in the correct place starting in the upper left corner and enter the formulae with Ctrl + Shift + Enter. This will put {} around the formula AFTER you enter it.

If you still have trouble. Upload your troublesome workbook and I will try and help you out.

**EJ101414** · 09-08-2014, 05:09 PM

Here is another table i am trying to populate with your solution. I have started but cant seem to finish correctly.

Thanks again.

**newdoverman** · 09-08-2014, 05:21 PM

You were addressing the wrong column to start with and that was about all that was wrong. Take a close look at the formula in the upper left of the chart to see how the formula differs from what you were trying.

Using a drop down list to display a data set/table

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Using a drop down list to display a data set/table

Re: Using a drop down list to display a data set/table

Re: Using a drop down list to display a data set/table

Re: Using a drop down list to display a data set/table

Re: Using a drop down list to display a data set/table

Re: Using a drop down list to display a data set/table

Re: Using a drop down list to display a data set/table

Re: Using a drop down list to display a data set/table

Re: Using a drop down list to display a data set/table

Thread Information

Users Browsing this Thread

Similar Threads

How to display data based on a selected month from a drop-down list

Drop-down list and respective data display

Data validation drop down list - vlookup / match criteria to display in list

how to display the correct row of data based on drop down list selection

Data Validation Drop Down List Display

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions