Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a new worksheet using predefined rows
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a new worksheet using predefined rows
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 dont 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. "Aaron" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum quarters to years; predefined shift of ranges | Excel Discussion (Misc queries) | |||
Creating a subtotal for an indeterminate number of rows | Excel Discussion (Misc queries) | |||
Creating Chart from Two Rows of Data | Excel Discussion (Misc queries) | |||
Creating a counter that does not include hidden rows | Excel Discussion (Misc queries) | |||
designing and saving predefined pivot table formats? | Excel Worksheet Functions |