Creating a drop down list that filters data based on selection??

Hi everyone,

I'm really hoping someone can help me out with this, I've been scratching my head with this one all morning.

Basically, my boss wants one excel sheet to display all information needed for several businesses we support.

My plan was to do as follows:
Create a main header with a list of all the information required for the businesses at the bottom. The business partner would select his/her business from a drop down on the right, and all the data pertaining to the business selected from the dropdown would be sorted/displayed at the bottom.
I currently have it where all the data is viewable and the business has to sort the column which applies to them, but I would rather clean this up and make it as user-friendly as possible.
I've attached a very basic example of what I'm trying to do, just in case the way I worded it isn't as understandable as I think (I'm somewhat of a beginner with Excel).

One option which would be even better was if I could keep the data on hidden sheets within the document (so they are easily modifiable by my group) but that when the business partner selects their business, the data is pulled from the hidden sheet and displayed at the bottom.

Any advice or tips would be greatly appreciated!!
Thanks!

This can be done easily with a pivot table. I don't know your familiarity with pivot tables, but here are the basic steps to set it up:

First, since it sounds like you'll be adding data on a regular basis you'll want to change your data range to a table so that new data entered will be automatically incorporated in your pivot table. Highlight all the data from your input sheet including the headers. On the Insert tab on the top ribbon select Table. Check the box that says "My table has headers". Press OK. At the top left of the screen you'll see a box under "Table Name:". Give your table a name (you cannot has spaces in the table name).

Go to a new sheet. Click in cell A1. On the Insert tab select Pivot Table. In the pop up window type the name of your table in the box for "Select table or range". Click OK.

You should now see the Pivot Table Field list on the right side of the screen.

Drag the "Business" header into the Report Filter box.
Drag all the other headers into the Row Labels box in the order you want them to appear.

On the top ribbon you should see a "Design" tab under a "Pivot Table Tools" header.
Click the button for "Report Layout" and select "Tabular Form".
Click the "Subtotals" button and select "Do Not Show Subtotals".

Now under the "Pivot Table Tools" header click the "Options" tab.
To the top left you'll see another button named "Options". Click on that.
In the pop up menu select "Layout and Format".
Uncheck the box next to "Autofit column width on update"
Click on the "Totals and Filters" tab and uncheck both "Show grand total for rows" and "Show grand total for columns".
Click OK.

Resize the columns so you can see all the data.

You're done.

At the top of the pivot table there should be a dropdown next to "Business". Select the business you'd like to show.

When new data is added to the data sheet, you'll just need to refresh the pivot table.
On the sheet with pivot table, click inside the pivot table.
Click the "Options" tab on the top ribbon. Then click the "Refresh" button.

If you want to hide the Field List or the + buttons you can do that from the "Options" tab as well.

Thank you for the detailed response!!

Just a few questions on this:

I had really simplified the data in the example provided, and unfortunately I'm having trouble applying your recommendations because of this. I've attached a new sheet showing a more accurate sample of data I'll be using.

What I'm struggling with is the fact that each business has its own column. I want to create a simple drop down menu that allows the business partner to select their own business and then only the products with an "x" under that business's column show up.

Because they are in separate columns, I'm struggling to figure out how I would add them to the pivot table so that it captures only the data that applies to them, but still keeping it in one "Business" dropdown menu.
So for example, Business A would select their business from a drop down that shows all businesses, and only the ref #, product and frequency columns that have an x under Business A would display for them.

I do feel like your solution would work, I just can't wrap my head around creating the pivot table to capture each individual business based on whether the ref #/product/frequency applies to them or not.

Having separate columns for each business is a whole different beast. I'm going to have to defer to someone else on this message board for a solution.

Ah darn, thanks for trying mo4391!

I learned something new today in the process of figuring this out regardless!