# find anywhere in A and B anything from C

• Re: find anywhere in A and B anything from C

Thnx very much will let you know how it worked
stay cool

• 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)

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.

## Files

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

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.

## Files

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

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

Can you plizz upload that last file again it is corrupted
thnx

• Re: find anywhere in A and B anything from C

here you go, try the new sort and fix routine with those 144. It shud work now.

## Files

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 is corrupted again ???

and thnx man I reall yapreciate this

• Re: find anywhere in A and B anything from C

voilà

## Files

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

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

once again, zip as portable, hope that helps

## Files

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

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

• Re: find anywhere in A and B anything from C

=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!