Filter data on multiple tabs based on selected criteria

February 10, 2012 at 17:36:22
Specs: Windows XP
Hi,

I am by no means an excel expert but I have a problem that I will likely lose sleep over until I find a solution.

I have a workbook with 4 tabs of data - each tab is set up the same with the same column headers.

I want to create a tab that will essentially search the other tabs and return rows of data based on criteria specified be selecting an option in a drop down menu. I have tried using the VLOOKUP function however I think this is too complex a situation for it. Pivot tables won't work either because I'm not trying to analze the data - I am simply trying to filter/seach the data and return all the information in a row as is. Essentially i need to autopopulate a table based on selected criteria.

How do I do this?

Thank you!

MRA


See More: Filter data on multiple tabs based on selected criteria

Report •


#1
February 11, 2012 at 11:49:45
You would need to use VBA (a macro).

The code could watch the cell with the drop down, and when it changed it would then search the other sheets, in order, and return any rows that contained a cell that match the data in the drop down.

In order for that code to be written, we would need to know some details.

What cell is the drop down in?

What column (or columns) on the other sheets would the matching data be found in?

Would you be appending the newly copied data to the bottom of the summary table or clearing it and replacing it only with rows based on the current selection?

Would you be copying values only or do the formulas in the copied rows need to be retained - or are there no formulas involved?

As you can see, VBA has to be told very specific information in order to perform the task. If there are any other details that you think we would need to know, please include them in your response.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 14, 2012 at 10:16:19
Hi,

Thanks for responding. I have never used VBA before but all the research I've been doing online points me in that direction also.

The excel document has 4 tabs of data in a table. The table headers are on the 4th and 5th row of every tab (same headers on all tabs)
There is a 5th tab that has the information for the data validation (used on all other tabs) - I wish I could attach my spreadsheet....

What I want to do is create a 6th "filter" tab as you know. Selecting one of the items from the drop-down menu (using the same data-validation boxes as are on the other tabs)...the user is able to pull out all the rows associated with what they have selected.

To answer your questions:
What cell is the drop down in?
On the 4 tabs with data there are drop down menus in columns D, E and H that the user can select. On the 6th tab I'm trying to create the drop down menu will be in cell D5 (when selected would filter by Department), E5 (to filter by market) and H5 (to filter by owner).

What column (or columns) on the other sheets would the matching data be found in?
Columns D, E and/or H . Each cell in these columns in the table are all drop-down menu's (with the data validation found in the 5th tab as mentioned earlier)

Would you be appending the newly copied data to the bottom of the summary table or clearing it and replacing it only with rows based on the current selection?
I'm not sure I understand this question....However....I want the summary/filter tab to pull up only data related to what the user selects from the drop-down menu at the top of the worksheet.

Would you be copying values only or do the formulas in the copied rows need to be retained - or are there no formulas involved?
There are formulas involved. Formulars are in columns K, M, O, Q, S, U, W and Y in all 4 of the tabs with data. I want to retain these formulas. A subtotal row at the bottom would be ideal (though not a game changer)


Does this help you to help me? :-S

Thanks,

m


Report •

#3
February 14, 2012 at 14:57:10
I wish I could attach my spreadsheet....

There are websites available where files can be stored. You would then post a link so we could download it.

Your answers help, but are also a bit confusing.

On the 6th tab I'm trying to create the drop down menu will be in cell D5 (when selected would filter by Department), E5 (to filter by market) and H5 (to filter by owner).

Are you asking that the data be copied from each sheet based on a single selection (fairly easy) or based on the combination of the 3 items selected by the 3 drop downs? (could be very complicated)

I want the summary/filter tab to pull up only data related to what the user selects from the drop-down menu at the top of the worksheet.

But there are 3 drop downs, aren't there? (this probably relates to my first question)

Formulars are in columns K, M, O, Q, S, U, W and Y in all 4 of the tabs with data. I want to retain these formulas.

When the formulas are copied, will they work in the new location?

For example, if a formula on Sheet2 reads =A1, it will return the value from Sheet2!A1.

If that formula is copied to Sheet6, it's going to return the value from Sheet6!A1.

Is that going to be a problem?

A subtotal row at the bottom would be ideal (though not a game changer)

Subtotaling what? Every column?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 16, 2012 at 06:34:18
Hi There, I'm so sorry if I made things more confusing.....

1. Are you asking that the data be copied from each sheet based on a single selection (fairly easy) or based on the combination of the 3 items selected by the 3 drop downs? (could be very complicated)

I would take either but at this point lets stick to the easier option - using only 1 of the drop downs. When an item selected in the drop down menu on the 6th tab matches data in a certain column on the first 4 tabs, I want the entire row of data to be extracted and placed on the 6th tab.

2. When the formulas are copied, will they work in the new location? For example, if a formula on Sheet2 reads =A1, it will return the value from Sheet2!A1.If that formula is copied to Sheet6, it's going to return the value from Sheet6!A1. Is that going to be a problem?

When formulas are copied they should work in the new location. They are simple formulas. Example:
Sheet 2 reads K7 = J7*I7 (same formula copied through the entire column K)
Sheet 6 column K would need to read the same formula so that whatever is in J is alwas multiplied by whatever is in I

3. A subtotal row at the bottom would be ideal (though not a game changer)
Subtotaling what? Every column?
totals the following columns - K, L, M, O, P, Q, S, T, U, V, X, Y

Basically what i need is for exaclty the same information on the data sheets to be extracted and relocated to the new sheet (formulas/functionality and all)

I wish I knew more about VBA to try and write this code.


Report •

Ask Question