Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extracting Data from Tables
Hi guys, I have put together a workbook to prepare quotations for electronic systems which looks something like this (Excel 2010) it's not formatted well in this post, but in this example there are 4 columns headed "Qty", "£ Each", "Description", "Total":-
Qty £ Each Description Total 1 £1,500.00 NR1 System Controller £1,500.00 0 £ 630.50 AR Audio Router 2 £1,200.00 Audio I/P Interface £2,400.00 1 £ 929.50 P500 1 x 500 Watt £ 929.50 0 £1,103.70 P250 1 x 250 Watt 2 £1,300.00 P125 1 x 125 Watt £2,600.00 0 £1,183.00 P60 1 x 60 Watt It is simple to select the quantity, which then calculates the total, as you can see some items are not included, but having them included in the list for selection acts as an aid de memoir to ensure the correct items are included in the overall system. I have included lots of other functions that automatically calculate the power consumption, equipment rack size, engineering time etc., dependent upon the items included. These figures appear in another tab in the workbook. The bit I'm stuck with is this; how can I copy the selected items (values, description, £) and omit those with zero quantity to another sheet, or even Word, so that I can use this to send to the customer. I've played around with VLOOKUP and PivotTable, but I'm not getting very far. Hope someone can point me in the right direction. Thanks, Charles |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Data from Tables
Just use datafilterautofilterNON blanksprintunfilter
Record a macro for the next one and assign to a button/shape On Jan 12, 6:39*am, <charles.turnergmx.com. wrote: Hi guys, I have put together a workbook to prepare quotations for electronic systems which looks something like this (Excel 2010) it's not formatted well in this post, but in this example there are 4 columns headed "Qty", "£ Each", "Description", "Total":- Qty £ Each Description Total 1 * * * £1,500.00 * * *NR1 System Controller £1,500.00 0 * * * £ 630.50 * * * AR Audio Router 2 * * * £1,200.00 * * *Audio I/P Interface £2,400.00 1 * * * £ 929.50 * * * P500 * *1 x 500 Watt * * £ 929.50 0 * * * £1,103.70 * * *P250 1 x 250 Watt 2 * * * £1,300.00 * * *P125 1 x 125 Watt * * * *£2,600.00 0 * * * £1,183.00 * * *P60 1 x 60 Watt It is simple to select the quantity, which then calculates the total, as you can see some items are not included, but having them included in the list for selection acts as an aid de memoir to ensure the correct items are included in the overall system. I have included lots of other functions that automatically calculate the power consumption, equipment rack size, engineering time etc., dependent upon the items included. These figures appear in another tab in the workbook. The bit I'm stuck with is this; how can I copy the selected items (values, description, £) and omit those with zero quantity to another sheet, or even Word, so that I can use this to send to the customer. I've played around with VLOOKUP and PivotTable, but I'm not getting very far. Hope someone can point me in the right direction. Thanks, Charles -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting data from one sheet based on data in another - VLookup? | Excel Worksheet Functions | |||
Extracting info to new tables | Excel Discussion (Misc queries) | |||
Extracting Data for .Txt Files By Unique Field Data | Excel Discussion (Misc queries) | |||
extracting from "show pages" pivot tables | Excel Programming | |||
help: extracting Execel tables with ADO without reordering them | Excel Programming |