find anywhere in A and B anything from C

    Re: find anywhere in A and B anything from C


    there could be a problem because in two groups there are 4 people, in one is 6 and in one 7 there are 4 groups


    And you said that function deals with that my suggestion that is cool


    thnx man for everything and can I somehow increase popularity or something in this forum so you will get more credits cause u really helped me bigtime
    stay cool
    saban

    Re: find anywhere in A and B anything from C


    here the newest version. half day absences possible now. this means that next to from till info also absence "rate" must be given. 1 = full absence, 0.5 = half absence. theoretically any fraction of a day is possible, entry allways as 0.xx.
    the sheet Groupdetails has a list of all groups with their size. For every group there is an absence list. groups are named aaa, bbb, etc for now, change as needed. On sheet aaa you will find the groups name in cell A1. The formula to detrmin the groupsize reads the lookup name from A1.
    sheet bbb is meant for the second grp, alas A1 reads bbb. the selfmade formulas refer to the absence list AbsGrp2, contrary to sheet aa reading from AbsGrp1
    Once you are sure everything looks as wanted you may copy new sheets for other groups. Mind, you have to ammend the groupname in A1 and the formulas (e.g. if you have copied sheet aaa then find/replac AbsGrp1 / Absgrp3 for Group ccc)
    the cells with "datum" and "rok" can be clicked for sorting by datum or rok. With that every second line is made yellow.
    Have fun.

    Re: find anywhere in A and B anything from C


    Thnx man very much for this
    I will check it out and let you know


    thnx again


    men something is wrong with that file I get this message that file is corupted (could you plizzz upload it again) when I tried to download it
    thnx


    never happened before
    this was the error
    The page you've been trying to access was blocked.


    Reason: The file you are trying to download is corrupted.
    The ID of the transaction is 35FA9698.

    Re: find anywhere in A and B anything from C


    thnx man this is working great as far as I check it out will let you know if I ran into some troubles but hopefully I will not
    You really have some awsome brain man :)


    thnx again.
    stay cool

    Re: find anywhere in A and B anything from C


    This reall y works as I wanted reall ycool


    Only have 2 more questions and that is it


    Did you notice when you put record in and the number of pages is at maximum
    you get "prost"(free) at all the cells in column where availability is shown is that OK or not ( try it: insert date lets say 20/06/2006 and 21/06/2006 and nr of pages is 48(at group where size is 8 people) and you will see that all column is filled with "prost"(green color), but if nr of pages is not at max then U get an error #VALUE - normally because no data are in that row


    and other question is: How could I include some chart on separate sheets which shows me how much is one group busy in current week (in % maybe)
    Would that be useful to know what do you think


    thnx man for all your help
    stay cool and hope to hear from you soon
    saban

    Re: find anywhere in A and B anything from C


    to avoid the value error you may extend all formulae to read:
    if(G5=""; here the present formula ;"")
    which means, that if rok is empty nothing is calculated.


    I will have a look at the chart issue tomorrow or monday.

    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 have dealt with #VALUE with conditonal formatin so now U can not see the error but it is still there but that will do.
    But it would be cool if I corrected that error I dont quite get which formulas should i correct to remove this #VALUE error(Maybe then it would also calculate faster cause now I have extended formulas to cell 2000 in each column and must say it works pretty slowly when it is calculating)


    Is this remainig capacity working ok even on long periods because if I write 22/06/2006 and dealine on 27/07/2006 I dont get right remainig capacity


    and thnx for everything

    Re: find anywhere in A and B anything from C


    I suppose your are referring to Col Q. This is only the remaining capacity on the last day this job is worked on, i.e. the date in col O. To get the remaining capacity till "rok" you need a new collumn (R) with formula =i5-c5.

    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


    Tried this formula in Column R but I think i do not get the right result either
    for date
    nr of p pg/day date assigned deadline cap till rok
    40 6 16/06/2006 19/06/2006
    199 6 19/06/2006 25/06/2006 [COLOR="Red"]40[/COLOR]
    Instead of 41 it should said 1


    Cause if I write nr.of pages 201 i get zaseden so ther is no more than one more page that translator can get


    thnx

    Re: find anywhere in A and B anything from C


    Can you plizz check this


    nr of pages: 40 date: 16/06/2006 deadline: 19/06/2006


    2nd document
    nr of pages: 200 date: 20/06/2006 deadline: 26/06/2006


    this should be maximum right


    I think something aint right I get "zaseden"


    thnx
    saban

    Re: find anywhere in A and B anything from C


    the error was in col L. now I think it should be ok.
    By the way, I have added the if functions to avoid value errors. Looks better and makes the file calculate faster.

    Re: find anywhere in A and B anything from C


    thnx man you the best will let u know if I ran into some trouble again
    stay cool


    saban

    Re: find anywhere in A and B anything from C


    how could i include charts for like how much is one group busy on certain week (any ideas-when u will have time it is not urgent)


    thnx

    Re: find anywhere in A and B anything from C


    sheet3 holds data series for group aaa and a matching chart. To see another month change the topmost date in Col A.

    Re: find anywhere in A and B anything from C


    thnx man u rule


    There is just one more thing that I would like to ask u: What is with this sort buttons are they necessary or can use this without sort buttons


    thnx again and stay cool
    saban



    Could u upload file again I get this error that file is corupted
    thnx

    Re: find anywhere in A and B anything from C


    depending on the order of the various lines results may defer. If the buttons don't serve you don't use them. Or even remove them.

    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


    so if I remove those sort buttons it should still work correctly right ?


    And can you pliz upload last file with charts again cause the current one is corrupted


    thnx for all your help
    saban

Participate now!

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

Register Yourself Login