# Posts by saban123

• ## Networkdays Function & Named Ranges

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

• ## find anywhere in A and B anything from C

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

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

• ## find anywhere in A and B anything from C

Re: find anywhere in A and B anything from C

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

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

• ## find anywhere in A and B anything from C

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

• ## find anywhere in A and B anything from C

Re: find anywhere in A and B anything from C

thnx will let you know how it worked

• ## check all cells in columns!!

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

• ## find anywhere in A and B anything from C

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 :

yours was:

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

• ## find anywhere in A and B anything from C

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

• ## find anywhere in A and B anything from C

Re: find anywhere in A and B anything from C

Aha that is cool so I just give a range of cells a name I didnt know that thnx
will try it and let you know

thnx for everything and stay cool
saban

• ## find anywhere in A and B anything from C

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

• ## find anywhere in A and B anything from C

Re: find anywhere in A and B anything from C

sorry for such a long response I just came from holidays (the short ones)
thnx for your patience and time I will check this out it looks very good
thnx for everything and stay cool

• ## find anywhere in A and B anything from C

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

• ## find anywhere in A and B anything from C

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

• ## find anywhere in A and B anything from C

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

• ## find anywhere in A and B anything from C

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

• ## check all cells in columns!!

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

• ## aplication or object defined error 1004

Re: aplication or object defined error 1004

It works now

• ## find anywhere in A and B anything from C

Re: find anywhere in A and B anything from C

Is this really so hard to check every cell in C column with every cell in A column and B column

• ## find anywhere in A and B anything from C

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

• ## find anywhere in A and B anything from C

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