I will ask Gord as well.
--
capt


"capt" wrote:

Thanks Herbert,
I shall try that. Seems very complicated though!!!
I shall let you know how I get on.
--
capt


"Herbert Seidenberg" wrote:

There are many formula solutions,
here is my very complex solution:
Name the columns as suggested in the last post.
Tools Options General R1C1 reference style.
(For location-independent formula appearance)
Name the 7 x 4 data array MyArray. (Don't include header)
Insert Name Define Names: RowR
Refers to: =ROW(INDEX(C1,1,):INDEX(C1,ROWS(MyArray),))
Create another 7 x 4 array and name it MyArray2.
Copy the formats and headers from MyArray to MyArray2.
Add a cell named TimeL , format it and enter 21:00.
Put a 7 x 1 helper column next to MyArray2 and name it RowTm
The array formula for RowTm is:
=SMALL(IF((Total_hrs<TimeL),(Total_hrs<TimeL)
*RowR,ROWS(MyArray)+1),RowR)
Select the first row of MyArray2 and
enter this array formula:
=IF(RowTm R<ROWS(MyArray)+1,INDEX(MyArray,RowTm R,),"")
Copy it down to the end of MyArray2.
Revert back to the A1 reference style.
The result will look like this:
Date Client Hours Total_hrs RowTm
5-Sep-07 A 5:20 5:20 1
7-Sep-07 B 5:15 10:35 2
8-Sep-07 A 10:05 20:40 3

VBA can do everything and anything.
For code, put yourself into the good hands of Gord Dibben.