Filtering data from one large table into many smaller ones based on certain criteria.

[hugedomer11]

I have a table for work that has 17 columns of information and each row represents a different contract. The columns contain information such as the date the contract was sent out, customer, salesperson, status, etc.

Is there a way to pull data from the main table into other worksheets based on certain criteria? For example I would like Sheet 1 to contain the master table with every contract and Sheet 2, 3, 4, etc. to have smaller tables that have only the contracts that pertain to Salesperson A, B, C, etc. I would like to be able to add contracts as they come in to the master table and have that information automatically show up on the pertinent sheets.

I found a way to do this with array formulas but array formulas are slow to calculate. This is the main piece of code the pulls the RC location of the information I want from the masterlist. Based on this location, I then use other formulas to copy the data from the master into the smaller tables.

=IF(ROW()-ROW(CQ)+1>ROWS('Master.xls'!LST)-COUNTBLANK('Master.xls'!LST),"",(ADDRESS(SMALL((IF('Master.xls'!LST="Salesperson A",ROW('Master.xls'!LST),ROW()+ROWS('Master.xls'!LST))),ROW()-ROW(CQ)+1),COLUMN('Master.xls'!LST),4)))

Thanks for your help.

[rylo]

Hi

One way would be to use a series of Pivot Tables and extract the relevant information for the nominates sales person. Refreshing the pivot table will bring in any updated information, and you can add a new sheet with a new table for any new salesperson.

HTH

rylo

[Palmetto]

I would like to be able to add contracts as they come in to the master table and have that information automatically show up on the pertinent sheets.

I think the Pivot Table suggestion from rylo is probably the best overall approach. In conjunction with the Pivot Table relative to adding more contracts, the PT source should be a dynamic named range.

By using a dynamic named range as the source, you only need to refresh the PT for the changes in the source data to refelct in the PT and this can be automated with simple VBA code.

If PT are not suitable, you could use automate the use of Advanced Filter with criteria in conjuction with using Data Validation drop downs to select the filter criteria.

[hugedomer11]

Is there a way to show text in the Data Field?

Thanks.

[rylo]

Hi

I would think so, but maybe I'm not interpreting your question correctly.

Can you generate an example workbook and attach to the post so we can see what you are working on.

rylo