Database Functions

[Guest]

I am using a table w/ the following data in columns A - C:

Prospect Name Week Created Status
1 11/6/06 Open
2 11/6/06 Open
3 11/6/06 Open
4 11/6/06 Open
5 11/6/06 Open
2 11/13/06 Open
5 11/13/06 Closed
6 11/13/06 Open
7 11/13/06 Open
8 11/13/06 Open

Each week I add five names to the list. As you can see some of those names
are repeats from the previous week (e.g., "2" & "5"). Is there a database
function that will tell me which names fell off the list from week to week
(e.g."1", "3" & "4") and which were added (e.g. "6", "7", "8")?

Also, is there a LOOKUP type function that will refer to the most recent
occurence of a "Prospect Name" and return the "Status" (e.g. a search for "5"
would result in "Closed"?

Thank you in advance for your assistance

 

[Biff]

Also, is there a LOOKUP type function that will refer to the most recent

occurence of a "Prospect Name" and return the "Status" (e.g. a search for
"5"
would result in "Closed"?

Try this:

E2 = lookup value = 5

=LOOKUP(2,1/(A2:A11=E2),C2:C11)

Is there a database function that will tell me
which names fell off the list from week to week
(e.g."1", "3" & "4") and which were added (e.g. "6", "7", "8")?

Possibly. Do you want to check the MOST RECENT data with the data from the
the previous week only? In other words, assume your sample data extended to
5 entries for 11/20/06. You want to check those entries against the entries
for 11/13/06 only? Are the dates *ALWAYS* a week apart like: 11/6, 11/13,
11/20, 11/27? If that's the case we can probably come up with something.

Biff

 

[Guest]

Thanks for your help. The dates are always a week apart and yes, I would
like to check the most recent data from the previous week only.