Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filters
i am trying to create a filter for an invoice that has over 650 items on it,
but i only want it to print the items that i am using for each invoice and not all the items listed |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filters
hi ryan
Does your invoice contain 650 items, or does your database or stock list contain that number. Are you trying to fill in an invoice, drawing from these items, where at any time you may want to use anything from 1 to ? items, depending on what is ordered? If true, you can use =VLOOKUP to get the info into your inovice. Say in Col A10 down you have Part Numbers. In B you want descriptions, in C you want unit selling price. You have a list of items on another sheet, and you have created a range name for this list, eg stocklist. Said stock list has columns A=Part Number, B=Description, C=Unit Cost, D=Unit Sell Price In B10 enter =IF(A10="","",VLOOKUP(A10,stocklist,2,0)) In C10 enter =IF(A10="","",VLOOKUP(A10,stocklist,4,0)) Copy down as far as you want to go. Enter a part number from the list, and you will get the description in B10 and the selling price in C10. You can also use data validation to select the part numbers from a dropdown list, however this will be more complex, as you do not want to scroll through 650 numbers, to select the last one! -- Hth Kassie Kasselman Change xxx to hotmail "ryan" wrote: i am trying to create a filter for an invoice that has over 650 items on it, but i only want it to print the items that i am using for each invoice and not all the items listed |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filters
Try this:
Sub ExportDatabaseToSeparateSheets() 'Export is based on the value in the desired column Dim myCell As Range Dim mySht As Worksheet Dim myName As String Dim myArea As Range Dim myShtName As String Dim KeyCol As Integer myShtName = ActiveSheet.Name KeyCol = InputBox("What column # within database to use as key?") Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1) For Each myCell In myArea On Error GoTo NoSheet myName = Worksheets(myCell.Value).Name GoTo SheetExists: NoSheet: Set mySht = Worksheets.Add(Befo=Worksheets(1)) mySht.Name = myCell.Value With myCell.CurrentRegion .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value .SpecialCells(xlCellTypeVisible).Copy _ mySht.Range("A1") mySht.Cells.EntireColumn.AutoFit .AutoFilter End With Resume SheetExists: Next myCell End Sub I forgot where I got this...probably this DG. Let me know if you have any questions. Ryan-- -- RyGuy "ryan" wrote: i am trying to create a filter for an invoice that has over 650 items on it, but i only want it to print the items that i am using for each invoice and not all the items listed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy with filters but not copy the filters in the middle? | Excel Discussion (Misc queries) | |||
Filters | Excel Discussion (Misc queries) | |||
Filters, Subtotal & Intacted Results after the filters' Removal | Excel Discussion (Misc queries) | |||
Filters | Excel Discussion (Misc queries) | |||
Using Filters | Excel Discussion (Misc queries) |