Posts by richadj4

  • Copy/Paste Cell Value to Correponding Dates Based On Month

    Re: Copy/Paste Cell Value to Correponding Dates Based On Month


    if you would prefer a non vba solution, then this formual would do the trick


    =IF($B4>C$3,IF($B4<=D$3,$A4,""),"")


    This does require a leading Jul - 07 to be entered in c3 but this column can be hidden (or just this cell changed to white text) to hide this.


    This formula can then be filled to all other cells and your figures will miraculously jump to the appropriate columns.


    David

  • Leading Zero In Date Formula

    Re: Leading Zero In Date Formula


    using a "format" in your formula will solve the problem


    =I35&H35&TEXT(G35,"dd")&C35&D35


    in fact you can make the whole thing easier with


    =TEXT(F35,"yyyymmdd")&C35&D35


    thus eliminating the need for the seperate day month and year columns.

  • Alternative To Full Column Reference In Formulas

    Re: Alternative To Full Column Reference In Formulas


    hmmmmm,


    That looks good, and theoretically it should help, but for some reason it doesn't.


    I've tried creating a named range and then when that didn't work, incorporating the range formula into my original formula, and I get a #value error.


    This is what I'm using
    =SUMPRODUCT(1*(base!$B$1:$B$19465=locations!B$1),1*(OFFSET(base!$A$1,0,0,COUNTA(base!$A:$A),1)=locations!$A2))


    this is different to the #NUM error I get with a full column range.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Ahhh, my bad, had some blank cells in there. working fine now.


    Solution for anyone else searching


    =SUMPRODUCT(1*(OFFSET(base!$B$1,0,0,MATCH("*",base!$B:$B,-1),1)=locations!B$1),1*(OFFSET(base!$A$1,0,0,MATCH("*",base!$A:$A,-1),1)=locations!$A2))


    Thanks Dave

  • Alternative To Full Column Reference In Formulas

    hi guys, I hope the thread title is appropriate, it took me almost 5 minutes to compose!!!


    I'm actually having difficulty with the sumproduct function, I'm trying to count items based on multiple criteria (and yes I have looked under that). I'm using a very helpful formula that I grabbed from this site.


    =SUMPRODUCT(1*(base!$B$1:$B$19465=locations!B$1),1*(base!$A$1:$A$19465=locations!$A6))


    The problem that I have is that this doesn't work if I replace $B$1:$B$19465 with $B:$B


    now as the number of rows on the base sheet may change, this means I need to change the formula each time I change the data on the base sheet which is somewhat ridiculous.


    I've been playing around with indirect trying to reference the last cell in a range, but I don't seem to be getting anywhere with that.


    Any suggestions?