find anywhere in A and B anything from C

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: find anywhere in A and B anything from C


    there is a little problem with
    =IF(C19=0;0;IF(C19=1;B19*C19-D19;B19)) i think so not sure
    but it says C19=1 but what if C19=2 which in most cases is when capacity is not at MAX


    Now it only shows capacity at 100% (when column is colored light blue) and it would be nice if it would show also half or third.. of it


    Is there a way to set this axis where 0,10,20...60 to 48 instead of 60 -but it is optional


    thnx man for all your help
    saban

    Re: find anywhere in A and B anything from C


    I realized that if documents are not sorted by deadline in ascending order it dont give me right results is that true ??(or did I messed something up again)
    could u check this out


    thnx

    Re: find anywhere in A and B anything from C


    the formula reads in plain language: if C19 = 0 then value = 0, else if c19 = 1 then c19 minus d19, else b19. Thus vakues of 2 or more are handled by the last else. . .


    give it a try and assign a job that takes less than a day and a second one starting at least one day later. E.G. you have a group of 8 with cap of 6 each and a job of 32 pages. Now the light blue will not cover all og the dark blue.


    Yes, sorting will give you better results. However it is not sure, that this sort will allways give best results. You may want to try to find other ways of sorting.

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: find anywhere in A and B anything from C


    I think I have 2 options one to sort by date and one to sort by deadline or do I have some other sort option


    And do you have any good sorting macro cause the xisting one sometimes doesnt want to sort (do you maybe know why is that)


    About this graph I will let you know how it worked
    thnx and stay cool

    Re: find anywhere in A and B anything from C


    see new file. sorting error fixed.
    Also column R improved. Now shows the actual remaining capacity til rok after the job on the respective line is finished. Result is negativ if J = Zaseden. The neg result tells how many pages cannot be translated untill rok.

    Re: find anywhere in A and B anything from C


    Brr Now i think I got it the right results are when it is sorted by date
    The dates that documents are assigned should be in ascending order
    I am testing it further and will let you know


    but i have a problem with this sort macro it does not sort the data according to date (and I think the best would be to sort just values from A to G)
    here is the macro

    Code
    Application.Calculation = xlCalculationManual
r = Range("c65536").End(xlUp).Row
Range("a5:G" & r).Select
    
    Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Key2:=Range("G5") _
        , Order2:=xlAscending, Key3:=Range("C5"), Order3:=xlDescending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
  Rows("4:" & r).Select


    Actually the problem is that it wont replace row 5 with row 6 if bigger date is in row 5
    and can it colour just cells from A to G and not whole row
    what do you think ?

    Re: find anywhere in A and B anything from C


    as allready done in my last upload you will have to replace header:= xlyes to xlno in the sorting part of the macro.

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: find anywhere in A and B anything from C


    thnx will let you know how it worked but it seems that documents should be sorted by date not by deadline to get right results but I dont know why is not always like that is there any method of sorting to show results 100% right

    Re: find anywhere in A and B anything from C


    sorting allone will not allways give wanted results. the attached does it better by trying to move "Zaseden" up if the above line is Ok befor deadline.


    maybe this can be improved further. I?ll see what I can do


    Mind: row 2 must remain empty.

    Re: find anywhere in A and B anything from C


    SKUPINA 1 48 6 BUDG 20/06/2006 26/06/2006
    SKUPINA 1 96 6 BUDG 19/06/2006 26/06/2006
    SKUPINA 1 144 6 BUDG 19/06/2006 27/06/2006

    Something is wrong at the last document translator can translate 144 pages


    But I get result "Zaseden"


    Any ideas why
    thnx

    Re: find anywhere in A and B anything from C


    SKUPINA 4 96 6 BUDG 19/06/2006 26/06/2006
    SKUPINA 4 48 6 BUDG 20/06/2006 26/06/2006
    SKUPINA 4 144 6 BUDG 19/06/2006 27/06/2006



    If I put documents in this order than it works OK (Says "free")-that is with your previous example because I cant download the new one it is still corrupted


    Any ideas why is that
    thanks

    Re: find anywhere in A and B anything from C


    thnx now it worked(I managed to download it)


    Is this fix routine runs with button or does it runs automaticly when value is changed or written. You say it should work correctly now?


    thnx for everything
    I will let u know how it worked


    does this new routine works only on sheet aaa maybe??


    This example has still =IF(G5="";"";I5-C5) in R column is this OK or did U accidentally upload the wrong one
    I cant figure out what have you changed in this example could u plizz briefly explain what have u changed??
    but somehow it works with this data I will test it with other and let you know


    thanks for all your help

    Re: find anywhere in A and B anything from C


    when I look at what I have uploaded col R reads
    =grpnetcap(H5;D5;O5;K5;PubHol;AbsGrp1)-P5
    which is what it should. Gives you the remaining capacity from enddate till rok.


    the fix routine is called by the sort by datum routine. have a look at VBA modul2 There might be instances where fix is not wanted, I am not sure. you can allways delete the last line in sort2() and call the fix manually, e.g. assign it to an additional button.


    fixroutine should work with all sheets it is called from.

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: find anywhere in A and B anything from C


    it works pretty ok it is just that I have this small square in row 2 and this fix routine always copy it to cells where I put data in it does this fix routine copy something from row 2 but I dont get why cause there is nothing important for calculation there ? and somehow it messes with conditional formating and messes the look of tables second row why is that


    Is it possible not to copy content of row 2 into cells and row 2 is always cleared from its formatting would it be possible to deal with that
    Is something wrong if I copy content of first cell "zaseden" into 4 row and not in second??????
    thnx
    saban

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login