hello again,

i may have been exagerating my work by having a large spreadsheet that rise
up to 15MB.
I can simplify my effort thru some formula from someone.

my reference Table....can reach 20000 rows...

e.g. A4:G2000

plan id rate name hrs amt start_date end_date
192 SC-001 $50 CCC 40 $2,000 1/22/2007 1/28/2007
192 SC-002 $70 BBB 40 $2,800 1/22/2007 1/28/2007
192 SC-003 $70 AAA 40 $2,800 1/22/2007 1/28/2007
191 SC-003 $70 AAA 40 $2,800 1/15/2007 1/21/2007
191 SC-002 $60 BBB 40 $2,400 1/15/2007 1/21/2007
191 SC-001 $50 CCC 40 $2,000 1/15/2007 1/21/2007
190 SC-002 $60 BBB 40 $2,400 1/8/2007 1/14/2007
190 SC-001 $40 CCC 40 $1,600 1/8/2007 1/14/2007
190 SC-003 $65 AAA 40 $2,600 1/8/2007 1/14/2007
189 SC-001 $40 CCC 40 $1,600 1/1/2007 1/7/2007
189 SC-002 $55 BBB 40 $2,200 1/1/2007 1/7/2007
189 SC-003 $60 AAA 40 $2,400 1/1/2007 1/7/2007
188 T-001 $30 DDD 40 $1,200 12/25/2006 12/31/2006
188 T-002 $40 EEE 40 $1,600 12/25/2006 12/31/2006
188 T-003 $50 FFF 40 $2,000 12/25/2006 12/31/2006
......
......
.....

on cell B1, i need a calculator formula that can produce the following text
results.
-----

Case 1) for past working personnel

if i type on A1 the id number "T-001"
the text result on B1 shall be something like this
"Mr. DDD has worked for 40 hrs. with base hourly rate of $30 since Plan# 188
from 12/25/2006 until 12/31/2006."

Case 2) for Currently working personnel

if i type on A1 the id number "SC-001"
the text result on B1 shall be something like this -
"Mr. CCC has worked for 160 hrs. with base hourly rates of $40 since Plan#
189 from 1/1/2007, $50 since Plan# 191 from 1/15/2007 up to present."

if i type on A1 the id number "SC-002"
the text result on B1 shall be something like this -
"Mr. BBB has worked for 160 hrs. with base hourly rate of $55 since Plan#
189 from 1/1/2007, $60 since Plan# 190 from 1/8/2007, $70 since Plan# 192
from 1/22/2007 up to present."

if i type on A1 the id number "SC-003"
the text result on B1 shall be something like this -
"Mr. AAA has worked for 160 hrs. with base hourly rate of $60 since Plan#
189 from 1/1/2007, $65 since Plan# 190 from 1/8/2007, $70 since Plan# 191
from 1/15/2007 up to present."

---
it may be better if the text result can include the total amount received
per year.

if required, the above can be expained farther.

regards,
driller
--
*****
birds of the same feather flock together..