Posts by lexusap

    Re: Sum Function based on changing value


    Hi,


    depending on your data, i would suggest you to change the formula to

    Code
    ...">="&EOMONTH(TODAY(),-1)+1....

    . This will automatically change the calculated day back to the first day of current month.


    Thanks
    Lex

    Re: Macro to transfer cell values from a column to last row of a table


    Hi,


    Just trying to guess what you want to do, You fill in data from Sheet 1 B2 to B5, then automatically copy paste to next available empty row on sheet 2.


    Code
    EOR1 = sheets("sheet2").cells(rows.count,1).end(xlup).row
    
    
    sheets("sheet1").range("B2:B5").copy sheets("sheet2").range("A" & EOR1 + 1)


    and the reason your macro is jumping to sheet 5. due to this code below

    Code
    Worksheets("sheet5").Select


    Cheers
    Lex

    Re: Sum Function based on changing value


    Hi,


    you can use this code

    Code
    =SUMIFS(C3:C9,B3:B9,">="&EOMONTH("1/1/2015",-1)+1,B3:B9,"<="&EOMONTH("1/6/2015",0))


    Note: C3:C9 = Sum Range
    B3:B9 = Date range 1
    ">="&EOMONTH("1/1/2015",-1)+1 = Criteria one - in this case. it means greater than !st of January
    B3:B9 = Date range 2
    "<="&EOMONTH("1/6/2015",0) = Criteria tow, in this case, it means less than equal to 30/06/2015


    EOMONTH function will give you the last of particular month.


    Cheers
    Lex

    Hi,


    I have trouble using Vlookup where I have to write Vlookup formula manually.
    Below is my current Vlookup formula and i need a formula where i dont have to change the word Jul/Aug/Sep/Oct and so on manually.


    Code
    =IFERROR(VLOOKUP(A5,'[Chemicals Budget 15-16 Monthly Data.xlsx]ATBJul'!$C:$E,3,FALSE),0)
    =IFERROR(VLOOKUP(A5,'[Chemicals Budget 15-16 Monthly Data.xlsx]ATBAug'!$C:$J,3,FALSE),0)
    =IFERROR(VLOOKUP(A5,'[Chemicals Budget 15-16 Monthly Data.xlsx]ATBSep'!$C:$J,3,FALSE),0)
    =IFERROR(VLOOKUP(A5,'[Chemicals Budget 15-16 Monthly Data.xlsx]ATBOct'!$C:$J,3,FALSE),0)
     and so on


    Cell H4 contains 1-Jul-14
    Cell I4 contains 1-Aug-14
    Cell J4 contains 1-Sep-14
    and so on


    I have tried using combination of

    Code
    =IFERROR(VLOOKUP(A5,'[Chemicals Budget 15-16 Monthly Data.xlsx]ATB&text(F4,"mmm")&'!$C:$E,3,FALSE),0)

    is not working.



    Any help would be appreciated.


    Thanks
    Albert

    Re: Complex SumIf -- Is it even possible?


    Hi Yoyo,


    based on your information given, we can use SUMIFS function.


    Code
    =SUMIFS(SUM_range,Vendor_Range, Vendor_lookup, Product_range, Product_lookup, Budget_range, Budget_lookup, Campaign_range, Campaign_lookup)


    Hope that helps.


    Cheers
    Lex

    Re: Adding IF statement to a Lookup


    Hi,


    Can it be replace by Vlookup instead of Lookup?
    Havent tested the code but this should work.


    Code
    =IF(C2="",VLOOKUP(A2,$F$1:$G$4,2,FALSE),"Revenue")
    or
    =IF(C2<>"","Revenue",VLOOKUP(A2,$F$1:$G$4,2,FALSE))
    or
    =IF(C2>0,"Revenue",VLOOKUP(A2,$F$1:$G$4,2,FALSE))


    Cheers
    Lex