Excel 2007 : Finding the highest value from a cell which is updated daily

Hi.
I have an excel doc with 2 sheets. The first sheet is a list of all of my salesmen and the second sheet is a list of the sales made on a particular day (one of the columns is which of the salesmen made the sale). I cut and paste the daily sales into the second sheet every morning.

What I am trying to do is write a formula to find the date of the most recent sale each of the salesmen have made. In orther words in Sheet1(A2) I would like it to look at Sheet2(A:A) and if the name in Sheet1(A1) is found to take the date, always found in Sheet2(D1) and put that date in Sheet1(A2).

I have had success with this, but have a huge problem. The way it stands now if a salesman doesn't make a sale in any given day then A2 becomes "False" because the 'If' condition isn't met. What I want to have happen is for it to leave the previous date that was in that cell.

Not sure if I'm explaining this well.

Please help.
Thanks.

I am currently doing this in 2 steps.
In the first step I find out how many sales the salesman in question made on the particular day. I use the following formula in Sheet1(C1):


=COUNTIF(Sheet2!A:A,Sheet1!A1)

Then I use a formula to grab the date if the result from that is more than 0. I use the following formula in Sheet1(B1):

=IF(C1>0,Sheet2!D1)

Where Sheet2!D1 is the date of the sales on Sheet 2.

There may be a smarter way of doing this.

I'd appreciate any help.

Thanks.

OK. Here goes.

Sheet 1

A B C
1 Joe 01/01/09 2
2 Frank False 0
3 Tom 01/01/09 4

Sheet One currently has a list of all of my salesman in column A, I would like to have a formula in each of the column B cells that will update to show the most recent sale made for that particular salesman. Currently I use column C to look in sheet two and find out how many sales an agent made on that particular date, then in column B I have an If function that states that if C>0 then write the new date. However, currently if C<=0 B it returns a Value of False, rather than leaving the date of the most recent sale (which would have been in that cell already, before I cut and paste the new records into Sheet 2).

The formula that I am currently using in the C column is:
=COUNTIF(Sheet2!A:A,Sheet1!A1)

The formula that I am currently using in the B column is:
=IF(C1>0,Sheet2!D1)




Sheet 2

A B C
1 01/01/09
2
3 Joe
4 Tom
5 Tom
6 Tom
7 Joe
8 Tom

Sheet 2 is updated daily. Each day I cut and paste the daily sales results into Sheet 2. B1 is always the date for the current results and column A is the list of who made each sale on that particular day.


Thanks.

Sorry, I don't know how to post so the table looks correct, this one looks better

Sheet 1
···············A···············B················C
1············Joe·········01/01/09··········2
2···········Frank··········False·············0
3···········Tom·········01/01/09··········4


Sheet 2
···············A···············B················C
1·························01/01/09·············
2··················································
3············Joe··································
4············Tom·································
5············Tom·································
6············Tom·································
7············Joe··································
8············Tom·································

Thanks...

Yes, I guess I will need to try and figure out how to do it using VBA. I will re-post where you suggested.

Thanks again.