Current Patient Count

[Dax Arroway]

Oh Excel Gurus, please hear my plea!

I've got sheet1 named Patients with
A = Name
B = StartDate
C = EndDate

I'm looking for a formula that I can enter on sheet2 (Current Client Count)
that looks at the Patients sheet and brings over the names for entries with
start dates but no end dates and ignores names with both start and end dates.


The point being that people start and are current. When they're done they
have an end date and are no longer current and should not be on the Current
Clinet Count sheet.

Can someone please help me with a formula that does this?

Thanks a million in advance!
Dax

 

[vezerid]

In Sheet2, say you start your current patient list in A2. In A2 the
following array formula (commit w/ Shift+Ctrl+Enter):

=INDEX(Sheet1!A2:A101,MATCH(1,(Sheet1!B2:B101<>"")*(Sheet1!C2:C101=""),
0))

In A3 the following formula to copy down (also array entered):

=INDEX(Sheet1!$A$2:$A$101,MATCH(1,(Sheet1!$B$2:$B$101<>"")*(Sheet1!$C
$2:$C$101="")*(COUNTIF($A$2:A2,Sheet1!$A$2:$A$101)=0),0))

Now Sheet2 should portray at any given moment the current patients.
Adjust the references 2:101 to suit.

HTH
Kostis Vezerides

 

[Dax Arroway]

I'm not sure if I'm doing this right or not but I'm getting an #NA error.
On Sheet1 I have:
A1:Name
B1:SDate (Column formatted as Date)
C1:FDate (Column formatted as Date)

A2:Frank
B2:11/05/08
C2:11/12/08

A3:Billy
B3:11/05/08
C3: (blank)

A4:Sue
B4:11/12/08
C4:11/12/08

A5oug
B5:10/15/08
C5:10/28/08

On Sheet2 selected cell A2 and hit ctrl+shift+enter and then typed:
A2: =INDEX(Sheet1!A2:A101,MATCH(1,(Sheet1!B2:B101<>"")*(Sheet1!C2:C101=""),0))
The above resulted in an #NA error

In cell A3 I entered:
=INDEX(Sheet1!$A$2:$A$101,MATCH(1,(Sheet1!$B$2:$B$101<>"")*(Sheet1!$C$2:$C$101="")*(COUNTIF($A$2:A2,Sheet1!$A$2:$A$101)=0),0))
This also resulted in an #NA error

I'm not sure what's wrong. Any help?

 

[Dax Arroway]

Never mind it works! I was hitting the shift+ctrl+enter too early. I typed
in the formula into the cell then while the cursor was still in the formula
box I hit the command and it worked! Stupid mistake. Thanks so much! --Dax

 

[vezerid]

I am glad. Thanks for the feedback.

Kostis

Never mind it works!  I was hitting the shift+ctrl+enter too early.  I typed
in the formula into the cell then while the cursor was still in the formula
box I hit the command and it worked!  Stupid mistake.  Thanks so much!  --Dax