Posts by jindon

    Hi,


    How about


    Select the range A1:C3 and type "cast", then


    Ctrl+Shift and press Enter to get out of the formula bar


    Otherwise only vba solution, I think


    jindon

    Hi, Kavir


    It is comfusing indeed.


    I think the beauty of using INDIRECT function is that it enables us to convert strings to the actual range reference in order to pass through other functions.


    INDIRECT(A9&"!j5") returns East!j5
    INDIRECT("A9"&"!j5") returns #REF!, because it is referring A9!j5


    INDIRECT("A9") returns East
    INDIRECT(A9) returns #REF!, becuase it refers the range named as East


    Once you use &, every string with "" will be recognised only as text, not as reference.
    So you cannot use " for the cell reference.


    I'm really not sure and I may be wrong, don't trust me.


    But this is how I understand this fuunction.


    jindon

    Kavir


    So sorry, I thought A9 would have a value like East!
    If you change the value in cell A9 to East!, should work, or otherwise,


    SUM(OFFSET(INDIRECT(A9&"!J5"),,,COUNT(INDIRECT(A9&"!J:J")),1))


    should give you the result.


    jindon

    Hi,


    If you have 2017 in cell A5, then probably like


    =SUMPRODUCT((INDIRECT("'R:\TCO ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!$A$14:$A$350")=$A5)*(INDIRECT("'R:\TCO ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!$C$14:$C$350")>=J$4)*(INDIRECT("'R:\TCO ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!$C$14:$C$350")<=DATE(YEAR(J$4),MONTH(J$4)+1,0))*(INDIRECT("'R:\TCO ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!$E$14:$E$350")))


    jindon

    Is this still going?


    Hi,


    If you want data in col. A, B, C, D, E and F on Sheet1 to be transferred to sheet2, the code should do it already.


    Have you replaced the code? I mean have you overwrite the code?
    You need to delete the old code and paste the new code to the same place.
    If you do so, it never work like old one.


    As you see in the code


    .Resize(, 6).Copy Destination:= _
    ws2.Range("a17")


    is carry the data only from col. A to F.


    Try copy the latest code again and replace the code.


    jindon

    Hi, James


    I didn't notice your second requirement,


    Quote from James


    I then want it to take the totals in column J and put it in the first tab in column S based again on the sequence number.


    if that is the main purpose, you don't need to run macro, the forumula will do the thing.


    jindon

    Hi,


    Right, up to col.F


    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
        Dim ws2 As Worksheet 
        Set ws2 = Sheets("Sheet2") 
        With Target 
            If .Column <> 1 Then Exit Sub 
            .Resize(, 6).Copy Destination:= _ 
            ws2.Range("a17") 
        End With 
        Set ws2 = Nothing 
    End Sub

    Hi, James
    here's a code


    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim ws2 As Worksheet
        Set ws2 = Sheets("Sheet2")
        With Target
            If .Column <> 1 Then Exit Sub
            .Resize(, 5).Copy Destination:= _
            ws2.Range("a17")
        End With
        Set ws2 = Nothing
    End Sub

    Hi,


    hope it works