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