Creating a new worksheet using predefined rows

[Guest]

My Question is how do I create a worksheet that contains only rows that
contain a certain name on a different worksheet.
I have an exported list of data that contains information for different
users. Would like a formula that will return the row only if it contains the
name desired.

once I figure out a way to creat that list I also want to take out any rows
that show a certain date. I'll attempt to make an exsample below, but the
real data will of course be much larger:

A B C D

Jim 03/26/07 Apples $200
Jim 03/27/07 Apples $200
Jane 03/25/07 Apples $200
Jim 03/23/07 Apples $200
Jim 03/25/07 Apples $200
Jane 03/23/07 Apples $200

Requesting to show only rows that show "Jim" + " before 03/26/07"

Result:

Jim 03/23/07 Apples $200
Jim 03/25/07 Apples $200


Thanks in advance. If you require further explaination please just ask. I
will attempt to be more descriptive.

 

[Guest]

Here is one possible approach:

Create a new sheet to display the desired data

Use Advanced filter to extract the rows (refer to Excel help)
You will need to set up condition/critera cells. It's up to you whether you
want to allow user to change the filter criteria. You can even hide the
criteria cells if desired

Update of the filter can be done in a macro, and this macro can be executed
every time you enter the sheet and upon change of the extract criteria.

Email me on (e-mail address removed) of you want a sample sheet for this...

 

[Guest]

Hi Aaron,

Pivot Table method: If you create a pivot table by selecting anywhere in
your original data and from the Data menu, select Pivot Table, Next and
Finish - Excel will create a new worksheet for the Pivot Table. Left click
and drag the Name column into the page portion of the pivot table; then click
and drag the Date and Fruit columns into the row portion of the pivot table
and finally click and drag the Amt column into the Data portion of the pivot
table.

Next select the Name Jim, then select the dates to display.

If you don't want certain sets of subtotals, you can mouse over the subtotal
and right click and select hide for each set of subtotal rows. Keep the
subtotals by Name.

Double left click on the total for Jim and Excel will automatically generate
the data that supports the subtotal.

Once you have finished this process, you can reuse the pivot table for
updated data. The condition will be that the column heads used in the pivot
table need to be labeled the same (they don’t have to me in any particular
order), no blank column heads – but new columns are ok.

If your data comes out periodically and you have a new last row or more rows
than the original data set - you can use the pivot table wizard to redefine
the range of your data or you can name the data range each time the data is
updated - then refresh your pivot tables. If you define your data as all rows
– it may slow down the performance of the pivot table.