Posts by saban123

    I have named range in column A = holfrom and another in column B = holtill


    When I try to use NETWORKDAYS(holfrom;holtill) I get an error VALUE
    Is it even possible to use this function with named ranges


    thnx for any solution

    Re: find anywhere in A and B anything from C


    You mean like excel is not apropriate tool anymore the things are getting to complexed


    this kinda gave me right results


    =(GrpSize-SUMPRODUCT((HOLfrom<=G2)*(HOLtill>=F2))/NETWORKDAYS(F2;G2)*NETWORKDAYS(AA3;AB3))*CapPerHead


    AA3=HOLfrom
    AB3=HOLtill


    It is like when translator is absent for whole period of translation of one document then the workrate should be deducted by 6 else should be deducted by workrate(6)/networkdays(date : deadline)


    Or something like that


    Thnx for all your help

    Re: find anywhere in A and B anything from C


    =(GrpSize-SUMPRODUCT((HOLfrom<=G5)*(HOLtill>=F5))/NETWORKDAYS(F5;G5))*CapPerHead


    Do you think this could work like i had in mind?(as described above)


    and do you have any idea how can I anmend this formula


    =(GrpSize-SUMPRODUCT((HOLfrom<=G2)*(HOLtill>=F2))/NETWORKDAYS(F2;G2)*NETWORKDAYS(AA2;AB2))*CapPerHead


    so that this part NETWORKDAYS(AA2;AB2)) will refer to holfrom and holtill on another sheet NETWORKDAYS(Sheet2! HOLfrom;HOLtill) or something like that

    Re: find anywhere in A and B anything from C


    But what is bothering me is that if one is on vacation for one day than it deduct workrate for 6 but even if translator is away for one day and the next day comes back it will consider as he didnt return the workarate would still be 42 instead of 48


    What do you think if I would divide 6/day difference(how many days is document being translated) and the workrate would be deducted for only 2,3 or 4... pages not always just 6


    Thnx

    Re: check all cells in columns!!


    Yeah something like that , sorry for such a long response I was on holidays for a couple of days (went to london)


    thnx for your help will let you know how it worked

    Re: find anywhere in A and B anything from C


    I have amended this formula in D column a lil bit but I dont think it is good :


    =(GrpSize-SUMPRODUCT((HOLfrom>=F5)*(HOLtill<=G5)))*CapPerHead

    yours was:


    =(GrpSize-SUMPRODUCT((HOLfrom<=F5)*(HOLtill>=F5)))*CapPerHead


    Which one is correct??


    Because with your formula I dont get any results when date for holidays is entered(I guess it was a typo on you side)


    But with my amended formula I get results except they are not quite correct


    Can you check this out and let me know
    thnx
    Saban

    Re: find anywhere in A and B anything from C


    Why cant I name that ranges from G3 to G5 with name HOLfrom? In capperhead and grpsize it shows names in name box but when it try to set name for HOLfrom or HOLtill it always shows just cell name like G4 or G5.. in name box I guess it should show HOLfrom or HOLtill so the formula on main sheet can recognize those ranges


    any ideas
    thnx

    Re: find anywhere in A and B anything from C


    But your example does not work ??
    (Have you just wrote what I should do or should this example work? Cause I get like class not registered or something when I open the file should there be any controls on this sheet(control buttons and so on)-failed to load activeX controls is the error)


    Is this formula correct in D column=(GrpSize-SUMPRODUCT((HOLfrom<=F5)*(HOLtill>=F5)))*CapPerHead
    What are those HOLfrom HOLtill CapPerhead should I write the formula with this records in it like HOLfrom= is on sheet2 in column G and so on.. or what or can I just leave it HOLfrom or HOLtill ??


    thnx

    Re: find anywhere in A and B anything from C


    It is a cool thing to show me how many translators are absent on that day but I dont need this I guess I need something that will find if date written in absence sheet is date between start and end date (date and deadline) in mainsheet and if it is then calculate new criteria regarding to how many translators are on vacation on that day


    So in main sheet I must find all documents that have date from absence sheet, between date and deadline on main sheet


    Am I thinking right??


    thnx and stay cool

    Re: find anywhere in A and B anything from C


    =SUMPRODUCT((HOLfrom<A7)*(HOLtill>A7)) ??
    what does this mean "HOLfrom", "HOLtill"


    I will send you sample of my workbook and where you deduct 6 from criteria 48 that means that it will deduct criteria for all days that document is being translated , I think I should do something like 6/networkdays(start d.;end d.)*nr of translators on vacation


    Can you pliz show me how would your example be implemented in workbook


    thnx for everything

    Re: find anywhere in A and B anything from C


    No man sorry it i snot quite what I am looking for actually I dont know anymore what the best solution for this holidays is?


    Do you have any ideas how can I do that with holidays:
    I wrote something like this:
    When date is between start and end date - first calculate day diference between start and end date, then divide 6/day diference * nr of heads
    and then I deduct this result from criteria(pages/day), so I get new criteria
    But I only manage to do this for one date for each document but I just cant figure out how to do this for multiple days (that translator is on vacation more than one day) :(


    I am lost


    Any Ideas

    Re: find anywhere in A and B anything from C


    Yes you are right and there is a problem how will calculations worked if more than one date in AE is TRUE I mean is between dates


    You see I am looking foe a solution that will:


    1.check if date is between this start and end date
    2.If it is between then deduct 6 from criteria-(how many pages can translator translate / day)
    3.Here is the problem: I would like to just write the days of holidays in a cells in column (AE) and then formulas should check wether criteria is to change or not if date in AE is Between start and end date then Yes change criteria (deduct it by 6) and now if there is another day in AE that fits between start and end date deuct criteria again - and that is the problem I cant find suitable solution for this


    All I want is to write holidays in column and formulas should check where do those dates corespond to start and end dates


    any ideas
    and thnx for all your help

    Hello


    I have a simple request which I cant figure out cause I am to stupid I guess:)


    Quote

    * A B C D-(this should check if number C between)
    1 10 14 19 TRUE
    2 15 17 FALSE
    3 20 22 13 TRUE
    4 25 27 24 TRUE


    I need results to look something like this


    I would like to check [COLOR="Red"]every[/COLOR] entry in C column to be validated against [COLOR="red"]all entries[/COLOR] in columns A and B (to se if nr. in C is between range A and B) and if it is then cell D should say TRUE in row where this number resides between lets say A1 and B2 or A2 and B2....


    Any ideas how to do that

    Re: find anywhere in A and B anything from C


    no no there could be a lot of entries in column C all that I need is that every entry in column C is checked (if it exists between dates) with every entry from column A to column B


    Here is the sample with some comments on what I am trying to do
    Sorry for my english

    Re: find anywhere in A and B anything from C


    it works only for each row separately I need that date in C is written maybe in C3 and then it checks this C3 with A1 and B1 and then with A2 and B2 and so on and then when in C2 date si written it is checked with dates from A1:B1 and A2:B2 .... and so on


    Do you know what I mean it is kinda hard to explain