Excel Formula Help

Shaun - Apr 15, 2010 at 02:45 PM
 Trowa - Apr 16, 2010 at 09:04 AM
Got a tough one here I'm trying to figure out.

Work Area Name Work Area Signout Date Completed Work Area #
New York Bob 4/14/2010 1
Washington Fred 4/14/2010 1
Chicago Mike 4/14/2010 1
Los Angeles Jerry 4/14/2010 1
Sheboygan Fred 4/14/2010 2
Boise Bob 4/15/2010 2
Cincinnati Mike 4/15/2010

Using Office 2007
Using columns B and C of the data I want to be able to generate the value in the last column to automatically fill in which work area this is that they are performing the work on where, for example, Mike has completed his first work area on 4/14/2010 and has just finished his second work area on 4/15/2010. I want the formula to first of all generate that Chicago was his first work area and then that Cincinnati is his second work area that he has completed and be able to do the same for all others on the table. There is also a high chance that one might finish 2 work areas on the same day so still need to be able to factor that in as well. Additionally there is no particular order in which the data in column A or B would be entered, it's just whoever happens to check out the next work area.
Just trying to make it easier to populate this column without having to spend time looking through each person's name manually to determine which work area it is that they have finished.
If it is not possible to create a formula like this then so be it, but i'm really interested to see if one can be made so this would work.


2 replies

sorry, hopefully you can make sense of my mock up spreadsheet, wasn't sure how it would format after I wrote it out
Hi shaun,

I would advise you to create a pivot table.

Here's an example for you. I've changed column D (Completed Work Area #) all to one to keep the calculations correct:


In cell B11 you can select a name. You can now see when he completed which work area as well as how many area's he has completed.

Play around with the options/setup of the pivot table to see what's possible.

Best regards,