Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Current Patient Count
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Current Patient Count
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 On Nov 18, 7:15*pm, Dax Arroway wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Current Patient Count
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 A5:Doug 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? "vezerid" wrote: 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 On Nov 18, 7:15 pm, Dax Arroway wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Current Patient Count
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 "Dax Arroway" wrote: 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 A5:Doug 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? "vezerid" wrote: 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 On Nov 18, 7:15 pm, Dax Arroway wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Current Patient Count
I am glad. Thanks for the feedback.
Kostis On Nov 18, 11:40*pm, Dax Arroway wrote: 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 "Dax Arroway" wrote: 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 A5:Doug 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? * "vezerid" wrote: 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 On Nov 18, 7:15 pm, Dax Arroway wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for current stock count needed | Excel Worksheet Functions | |||
How do I count the number of even weeks in the current month. | Excel Worksheet Functions | |||
Count number of days between dates BUT IF null to current date | Excel Worksheet Functions | |||
Create patient schedule based on master therapist schedule | Excel Discussion (Misc queries) | |||
I need to exclude duplicate patient names for dates of service is. | Excel Discussion (Misc queries) |