Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter - Excel 2003
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 & 4 are needed in the filtered results. Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location. I wish to place on sheet 2 all records shown as "Out". I've tried the VLOOKUP but guess I'm not getting the right parameters. Help appreciated. Thanks. PS - I'm working with Excel 2007, but the workbook has to be compatible with 2003. -- Wercs |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter - Excel 2003
Hi,
Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C. Please perform the following steps 1. Assign a column heading to all the columns in sheet 1, if not already done so. Say the heading are A-M 2. Now type A in a cell below the data, say A25 3. In cell A26, type Out 4. In sheet 2, type A,B and D in B3,C3 and D3. 5. Now click on cell B4 on sheet 2 and go to Data Filter Advance Filter; 6. Select the radio button for "Copy to another location"; 7. In the list box, please select the range on sheet 1 (Please select the header row as well); 8. In the Criteria box, select the range A25:A26 of sheet 1; 9. In the copy to box, select B3:D100 of sheet 2; 10. Click on OK Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Wercs" wrote in message ... I wish to filter results from one sheet onto a second sheet. I have a data table containing 13 columns, but only columns 1,2 & 4 are needed in the filtered results. Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location. I wish to place on sheet 2 all records shown as "Out". I've tried the VLOOKUP but guess I'm not getting the right parameters. Help appreciated. Thanks. PS - I'm working with Excel 2007, but the workbook has to be compatible with 2003. -- Wercs |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter - Excel 2003
Hi there,
Good work. This is exactly what's needed. Thanks a lot! Regards, -- Wercs. "Ashish Mathur" wrote in message ... Hi, Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C. Please perform the following steps 1. Assign a column heading to all the columns in sheet 1, if not already done so. Say the heading are A-M 2. Now type A in a cell below the data, say A25 3. In cell A26, type Out 4. In sheet 2, type A,B and D in B3,C3 and D3. 5. Now click on cell B4 on sheet 2 and go to Data Filter Advance Filter; 6. Select the radio button for "Copy to another location"; 7. In the list box, please select the range on sheet 1 (Please select the header row as well); 8. In the Criteria box, select the range A25:A26 of sheet 1; 9. In the copy to box, select B3:D100 of sheet 2; 10. Click on OK Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Wercs" wrote in message ... I wish to filter results from one sheet onto a second sheet. I have a data table containing 13 columns, but only columns 1,2 & 4 are needed in the filtered results. Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location. I wish to place on sheet 2 all records shown as "Out". I've tried the VLOOKUP but guess I'm not getting the right parameters. Help appreciated. Thanks. PS - I'm working with Excel 2007, but the workbook has to be compatible with 2003. -- Wercs |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter - Excel 2003
Just a point:
When data in sheet 1 changes from In to Out (or vice versa), how can the filtered results on sheet 2 be similarly updated automatically? Rgs. "Ashish Mathur" wrote in message ... Hi, Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C. Please perform the following steps 1. Assign a column heading to all the columns in sheet 1, if not already done so. Say the heading are A-M 2. Now type A in a cell below the data, say A25 3. In cell A26, type Out 4. In sheet 2, type A,B and D in B3,C3 and D3. 5. Now click on cell B4 on sheet 2 and go to Data Filter Advance Filter; 6. Select the radio button for "Copy to another location"; 7. In the list box, please select the range on sheet 1 (Please select the header row as well); 8. In the Criteria box, select the range A25:A26 of sheet 1; 9. In the copy to box, select B3:D100 of sheet 2; 10. Click on OK Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Wercs" wrote in message ... I wish to filter results from one sheet onto a second sheet. I have a data table containing 13 columns, but only columns 1,2 & 4 are needed in the filtered results. Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location. I wish to place on sheet 2 all records shown as "Out". I've tried the VLOOKUP but guess I'm not getting the right parameters. Help appreciated. Thanks. PS - I'm working with Excel 2007, but the workbook has to be compatible with 2003. -- Wercs |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter - Excel 2003
Hi,
the Advanced Filter command is not a formula, so the second sheet will not update automatically. You can rerun the filter command when the data changes or you can record a macro that reruns the command for you. If you need it to have a formula approach, its going to be fairly complicated, so hopefully you can live with the above solution. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Wercs" wrote: Just a point: When data in sheet 1 changes from In to Out (or vice versa), how can the filtered results on sheet 2 be similarly updated automatically? Rgs. "Ashish Mathur" wrote in message ... Hi, Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C. Please perform the following steps 1. Assign a column heading to all the columns in sheet 1, if not already done so. Say the heading are A-M 2. Now type A in a cell below the data, say A25 3. In cell A26, type Out 4. In sheet 2, type A,B and D in B3,C3 and D3. 5. Now click on cell B4 on sheet 2 and go to Data Filter Advance Filter; 6. Select the radio button for "Copy to another location"; 7. In the list box, please select the range on sheet 1 (Please select the header row as well); 8. In the Criteria box, select the range A25:A26 of sheet 1; 9. In the copy to box, select B3:D100 of sheet 2; 10. Click on OK Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Wercs" wrote in message ... I wish to filter results from one sheet onto a second sheet. I have a data table containing 13 columns, but only columns 1,2 & 4 are needed in the filtered results. Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location. I wish to place on sheet 2 all records shown as "Out". I've tried the VLOOKUP but guess I'm not getting the right parameters. Help appreciated. Thanks. PS - I'm working with Excel 2007, but the workbook has to be compatible with 2003. -- Wercs |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter - Excel 2003
Hi,
I understand about the filter not being a formula. Just how complicated would the formula approach be? As the data changes quite regularly, this would be the preferred method, if at all possible. Regards, -- Wercs. "Shane Devenshire" wrote in message ... Hi, the Advanced Filter command is not a formula, so the second sheet will not update automatically. You can rerun the filter command when the data changes or you can record a macro that reruns the command for you. If you need it to have a formula approach, its going to be fairly complicated, so hopefully you can live with the above solution. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Wercs" wrote: Just a point: When data in sheet 1 changes from In to Out (or vice versa), how can the filtered results on sheet 2 be similarly updated automatically? Rgs. "Ashish Mathur" wrote in message ... Hi, Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C. Please perform the following steps 1. Assign a column heading to all the columns in sheet 1, if not already done so. Say the heading are A-M 2. Now type A in a cell below the data, say A25 3. In cell A26, type Out 4. In sheet 2, type A,B and D in B3,C3 and D3. 5. Now click on cell B4 on sheet 2 and go to Data Filter Advance Filter; 6. Select the radio button for "Copy to another location"; 7. In the list box, please select the range on sheet 1 (Please select the header row as well); 8. In the Criteria box, select the range A25:A26 of sheet 1; 9. In the copy to box, select B3:D100 of sheet 2; 10. Click on OK Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Wercs" wrote in message ... I wish to filter results from one sheet onto a second sheet. I have a data table containing 13 columns, but only columns 1,2 & 4 are needed in the filtered results. Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location. I wish to place on sheet 2 all records shown as "Out". I've tried the VLOOKUP but guess I'm not getting the right parameters. Help appreciated. Thanks. PS - I'm working with Excel 2007, but the workbook has to be compatible with 2003. -- Wercs |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter - Excel 2003
You have 3 columns you want to extract data from. How many rows of data need
to be searched and approximately how many rows of results do you expect to be extracted? The formula to do this isn't that complicated but it is calculation intensive on large amounts of data. If you have 1000's of rows of data using a formula may not be the best way to go. -- Biff Microsoft Excel MVP "Wercs" wrote in message ... Hi, I understand about the filter not being a formula. Just how complicated would the formula approach be? As the data changes quite regularly, this would be the preferred method, if at all possible. Regards, -- Wercs. "Shane Devenshire" wrote in message ... Hi, the Advanced Filter command is not a formula, so the second sheet will not update automatically. You can rerun the filter command when the data changes or you can record a macro that reruns the command for you. If you need it to have a formula approach, its going to be fairly complicated, so hopefully you can live with the above solution. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Wercs" wrote: Just a point: When data in sheet 1 changes from In to Out (or vice versa), how can the filtered results on sheet 2 be similarly updated automatically? Rgs. "Ashish Mathur" wrote in message ... Hi, Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C. Please perform the following steps 1. Assign a column heading to all the columns in sheet 1, if not already done so. Say the heading are A-M 2. Now type A in a cell below the data, say A25 3. In cell A26, type Out 4. In sheet 2, type A,B and D in B3,C3 and D3. 5. Now click on cell B4 on sheet 2 and go to Data Filter Advance Filter; 6. Select the radio button for "Copy to another location"; 7. In the list box, please select the range on sheet 1 (Please select the header row as well); 8. In the Criteria box, select the range A25:A26 of sheet 1; 9. In the copy to box, select B3:D100 of sheet 2; 10. Click on OK Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Wercs" wrote in message ... I wish to filter results from one sheet onto a second sheet. I have a data table containing 13 columns, but only columns 1,2 & 4 are needed in the filtered results. Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location. I wish to place on sheet 2 all records shown as "Out". I've tried the VLOOKUP but guess I'm not getting the right parameters. Help appreciated. Thanks. PS - I'm working with Excel 2007, but the workbook has to be compatible with 2003. -- Wercs |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filter in excel 2003 | Excel Discussion (Misc queries) | |||
Can't Filter in Excel 2003 | Excel Discussion (Misc queries) | |||
Is there an Excel 2003 Advanced Filter Bug?! | Excel Discussion (Misc queries) | |||
need pcx graphics filter for excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 Filter Problem | Excel Discussion (Misc queries) |