Posts by jindon

    Hi,


    you don't need to SELECT


    Private Sub CommandButton1_Click()
    Range("A1:N37").Select
    Selection.PrintOut Copies:=1, Collate:=True
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    With Sheets("Delivery Data")
    .Rows("1").ClearContents
    .Rows("2").Copy destination:=.Rows("1")
    Application.CutCopyMode = False
    .Rows("2").delete
    End With
    Sheets("Delivery Note").Select
    End Sub


    hope it works

    Hi,


    excuse me for interrupting.


    easy bit by the formula


    if
    Names in Col.A
    P/L figures in Col.B
    then


    Top 10
    c1:
    =INDEX($A:$B,MATCH(LARGE($B:$B,ROW(A1)),$B:$B,0),COLUMN(A1))
    then fill right and fill down to No.10


    Bottom 10
    =INDEX($A:$B,MATCH(SMALL($B:$B,ROW(A1)),$B:$B,0),COLUMN(A1))
    do the same as above


    note: if there's 2 or more records which have identical P/L amount, only the first record
    will be displayed, therefore you need to modify maybe by cents.


    ???
    jindon

    Hi


    Worksheet_Change event shoud recognize


    try following code and insert a row somewhere



    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Range("a1").Value = Range("a1").Value + 1
    Application.EnableEvents = True
    End Sub

    Hi,


    =IF(Retail<=500,DeliveryCharge,0)*OR(Distance>=10,DeliveryCharge,0)


    would you like to tell us the logic?


    jindon

    Hi,


    you can rewrite as


    if(indirect("worksheetA!A"&31)="s",indirect("worksheetA!B"&31),"-")


    "row(a31) is only the substitute of 31"


    =if(indirect("worksheetA!A"&row(A31))="s",indirect("worksheetA!B"&row(a31)),"-")


    the reason why I used row(a31) is that if you set
    =if(indirect("worksheetA!A"&row(A1))="s",indirect("worksheetA!B"&row(a1)),"-")
    in cell A1, you can drag down and it increases as you go down.


    hope it helps

    Hi,


    here's another one


    Hi,


    Really sorry that I had mistake in my formula,


    should be


    =IF(COUNTIF(Business!$A$1:$A$1000,$A$1)>=ROW(A1),INDEX(Business!$A$1:$D$1000,SMALL(IF(Business!$A$1:$A$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1)),COLUMN(A1)),"")


    please note: the formula above must be in the first row which you desired to display the data from. it doesn't matter of which ROW. If you start to display the data from A3, put the formula in A3. Row(a1) and column(a1) are not related to row and column, they are substitute of numbers and they increase as you drag. If you want to change the sheet name and criteria, YES, you can but others, absolutely NOT.


    regards,
    jindon

    Hi,


    Add new sheet


    in cell A2
    =IF(COUNTIF(Business!$A$1:A1000,$A$1)>=ROW(A1),INDEX(Business!$A$1:$D$1000,SMALL(IF(Business!$A$1:$A$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1)),COLUMN(A1)),"")


    holding down Ctrl+Shift keys and press Enter to get out from the formula bar


    select cell A2 and drag to the right up to cell D2, then drag down as much as you need.


    Set the criteria in cell A1 e.g. Expense A


    copy entire sheet and add another sheet and paste, then set A1 again e.g. Expense B


    note: if you need to expand the data range, you must expand every 1000 in the formula and they must be identical.


    hope it helps

    Hi,


    try the code


    Hi,


    in cell F2
    =IF(COUNTIF(E$2:E2,E2)=1,ROW(),"") and drag down to the last row of the data in Col.A


    in cell G2
    =IF($F2<>"",SUMIF($E:$E,$E2,A:A),"") and drag to the right up to cell J2


    in cell K2
    =IF($F2<>"",E2,"")


    then, select cells from G2 to K2 and drag down as long as you need.
    select entire columns from F to K --> copy --> pastescpecial/values
    sort by col.F in ascending order


    if you don't need origial data, just delete columns from A to F


    jindon

    correct.


    R1C1=$A$1
    another method is to name the range like
    Sheets("SheetName").Range("A1:Z1000").name = "List"


    then you can use it in the formula. like


    "=VLOOKUP(RC[-4],List,26,TRUE)"

    if the worksheets like


    Sheet1, Sheet2, Sheet3, Sheet4, Sheet5 then
    cell in Sheet5
    =sum(Sheet1:Sheet4!A1)


    Tom, Nancy, Henry, Charles, Total then
    cell in Total
    =sum(Tom:Charles!A1)


    if you add new sheet in between the firstsheet and last sheet
    i.e. Sheet1 and Sheet4 or Tom and Charles
    it will be automatically included