Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula
I have a spreadsheet with numerous sheets. In one sheet I have a list of
patients who have a number of visit dates. The columns in spreadsheet would have Pt. # Visit Date 1, Visit Date 2 up to maybe 20 visits. I have another sheet in the workbook that has a column named €śLast Visit Date€ť. I want to add a formula that will look across the row in the 1st sheet and enter the last date into the €śLast Visit Date€ť in the second sheet. For example if one patient has 20 visits the formula will look across the row and all the visits and if the last visit was visit number 10 it would enter that date into the €ślast visit date€ť in the other sheet. I am using Excel 2003. Thank you in advance for any help. Best regards, Dee |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula
Dee,
You didn't provide sheet names or data ranges, so... In this example, I used 3 patients and 13 future appointments Sheet1 = a table of all patients and dates of their future visits --Row 1 is column lables (insignificant): PatientNbr, Appt1, Appt2.....Appt13 --Column A is the list of 3 patients --B2:N4 = Dates of patients future appointments Sheet2 = a list of all patients and the formula to return their last appointment on the books (Max(Date)) --Row 1 is column lables (insignificant): PatientNbr, Last Visit Date --Column A is the list of 3 patients -- In B2, enter this formula: =SUMPRODUCT(MAX((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2 :$N$4))) --Copy this formula down into cells B3 & B4 Now you will have to expand this these ranges so they fit your situation. I used 13 appointments, that equals column N. You will have to expand this out to column U (If your first appointment is in column B). Also, expand it to include all of your patients. Now, if you add patients from time to time or decide to expand this to 25 future appointments, then you will have to readjust your formula each time. But there is a way to make that dynamic if you wanted to. If interested, please write back, but provide more info: Sheet Names and data ranges. HTH, Conan "Dee" wrote in message ... I have a spreadsheet with numerous sheets. In one sheet I have a list of patients who have a number of visit dates. The columns in spreadsheet would have Pt. # Visit Date 1, Visit Date 2 up to maybe 20 visits. I have another sheet in the workbook that has a column named "Last Visit Date". I want to add a formula that will look across the row in the 1st sheet and enter the last date into the "Last Visit Date" in the second sheet. For example if one patient has 20 visits the formula will look across the row and all the visits and if the last visit was visit number 10 it would enter that date into the "last visit date" in the other sheet. I am using Excel 2003. Thank you in advance for any help. Best regards, Dee |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula
Hi Conan,
The sheet with the patient number and visit dates is called Consented Patients Pt. # Visit 1 Visit 2 Visit 3 Visit 4 etc. 123 12/10/07 1/15/08 1/25/08 124 9/30/07 11/25/07 The second sheet is named Vital Status Pt. # Site # Country Last Visit Date 123 124 I would like the formula to look in the row for each pt. # and record the last visit date for that patient which for pt 123 would be 1/25/08. Thanks very much for your help, I really appreciate it. Best regards, Dee "Conan Kelly" wrote: Dee, You didn't provide sheet names or data ranges, so... In this example, I used 3 patients and 13 future appointments Sheet1 = a table of all patients and dates of their future visits --Row 1 is column lables (insignificant): PatientNbr, Appt1, Appt2.....Appt13 --Column A is the list of 3 patients --B2:N4 = Dates of patients future appointments Sheet2 = a list of all patients and the formula to return their last appointment on the books (Max(Date)) --Row 1 is column lables (insignificant): PatientNbr, Last Visit Date --Column A is the list of 3 patients -- In B2, enter this formula: =SUMPRODUCT(MAX((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2 :$N$4))) --Copy this formula down into cells B3 & B4 Now you will have to expand this these ranges so they fit your situation. I used 13 appointments, that equals column N. You will have to expand this out to column U (If your first appointment is in column B). Also, expand it to include all of your patients. Now, if you add patients from time to time or decide to expand this to 25 future appointments, then you will have to readjust your formula each time. But there is a way to make that dynamic if you wanted to. If interested, please write back, but provide more info: Sheet Names and data ranges. HTH, Conan "Dee" wrote in message ... I have a spreadsheet with numerous sheets. In one sheet I have a list of patients who have a number of visit dates. The columns in spreadsheet would have Pt. # Visit Date 1, Visit Date 2 up to maybe 20 visits. I have another sheet in the workbook that has a column named "Last Visit Date". I want to add a formula that will look across the row in the 1st sheet and enter the last date into the "Last Visit Date" in the second sheet. For example if one patient has 20 visits the formula will look across the row and all the visits and if the last visit was visit number 10 it would enter that date into the "last visit date" in the other sheet. I am using Excel 2003. Thank you in advance for any help. Best regards, Dee |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula
Dee,
Use the formula I posted earlier. I'll see if I can adjust it for your sheet names, but I can't adjust it to include your whole list of patients This assumes that column labels are in row 1 on both sheets and patient numbers are in column A starting on row 2 in both sheets. Enter the following formula in cell D2 ("Last Visit Date" column) on the "Vital Status" sheet: =SUMPRODUCT(MAX(('Consented Patients'!$A$2:$A$4=$A2)*('Consented Patients'!$B$2:$N$4))) Once again, expand the ranges referenced to include all columns of "Visits" and all rows of patients (my example uses 3 patients & 13 visits). Also, reread my notes at the bottom of my previous post concerning adding new patients over time or expanding to 25 visits instead of 20, and dealing with the expansion dynamically. HTH, Conan "Dee" wrote in message ... Hi Conan, The sheet with the patient number and visit dates is called Consented Patients Pt. # Visit 1 Visit 2 Visit 3 Visit 4 etc. 123 12/10/07 1/15/08 1/25/08 124 9/30/07 11/25/07 The second sheet is named Vital Status Pt. # Site # Country Last Visit Date 123 124 I would like the formula to look in the row for each pt. # and record the last visit date for that patient which for pt 123 would be 1/25/08. Thanks very much for your help, I really appreciate it. Best regards, Dee "Conan Kelly" wrote: Dee, You didn't provide sheet names or data ranges, so... In this example, I used 3 patients and 13 future appointments Sheet1 = a table of all patients and dates of their future visits --Row 1 is column lables (insignificant): PatientNbr, Appt1, Appt2.....Appt13 --Column A is the list of 3 patients --B2:N4 = Dates of patients future appointments Sheet2 = a list of all patients and the formula to return their last appointment on the books (Max(Date)) --Row 1 is column lables (insignificant): PatientNbr, Last Visit Date --Column A is the list of 3 patients -- In B2, enter this formula: =SUMPRODUCT(MAX((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2 :$N$4))) --Copy this formula down into cells B3 & B4 Now you will have to expand this these ranges so they fit your situation. I used 13 appointments, that equals column N. You will have to expand this out to column U (If your first appointment is in column B). Also, expand it to include all of your patients. Now, if you add patients from time to time or decide to expand this to 25 future appointments, then you will have to readjust your formula each time. But there is a way to make that dynamic if you wanted to. If interested, please write back, but provide more info: Sheet Names and data ranges. HTH, Conan "Dee" wrote in message ... I have a spreadsheet with numerous sheets. In one sheet I have a list of patients who have a number of visit dates. The columns in spreadsheet would have Pt. # Visit Date 1, Visit Date 2 up to maybe 20 visits. I have another sheet in the workbook that has a column named "Last Visit Date". I want to add a formula that will look across the row in the 1st sheet and enter the last date into the "Last Visit Date" in the second sheet. For example if one patient has 20 visits the formula will look across the row and all the visits and if the last visit was visit number 10 it would enter that date into the "last visit date" in the other sheet. I am using Excel 2003. Thank you in advance for any help. Best regards, Dee |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula
Hi Conan,
I tried the formula you gave me and received and #Ref error. The workbook I have has the following: in the consented patients sheet the pt numbers are in A4 -A181 and the visits go all the way from D4-AO181 The formula I put in the cell G3 on the Vital status sheet was: =SUMPRODUCT(MAX(('Consented Patients'!$A$4:$A$181=$A4)*('Consented Patients'!$D$4:$AO$181))) Thanks very much for your help. Best regards Dee "Conan Kelly" wrote: Dee, Use the formula I posted earlier. I'll see if I can adjust it for your sheet names, but I can't adjust it to include your whole list of patients This assumes that column labels are in row 1 on both sheets and patient numbers are in column A starting on row 2 in both sheets. Enter the following formula in cell D2 ("Last Visit Date" column) on the "Vital Status" sheet: =SUMPRODUCT(MAX(('Consented Patients'!$A$2:$A$4=$A2)*('Consented Patients'!$B$2:$N$4))) Once again, expand the ranges referenced to include all columns of "Visits" and all rows of patients (my example uses 3 patients & 13 visits). Also, reread my notes at the bottom of my previous post concerning adding new patients over time or expanding to 25 visits instead of 20, and dealing with the expansion dynamically. HTH, Conan "Dee" wrote in message ... Hi Conan, The sheet with the patient number and visit dates is called Consented Patients Pt. # Visit 1 Visit 2 Visit 3 Visit 4 etc. 123 12/10/07 1/15/08 1/25/08 124 9/30/07 11/25/07 The second sheet is named Vital Status Pt. # Site # Country Last Visit Date 123 124 I would like the formula to look in the row for each pt. # and record the last visit date for that patient which for pt 123 would be 1/25/08. Thanks very much for your help, I really appreciate it. Best regards, Dee "Conan Kelly" wrote: Dee, You didn't provide sheet names or data ranges, so... In this example, I used 3 patients and 13 future appointments Sheet1 = a table of all patients and dates of their future visits --Row 1 is column lables (insignificant): PatientNbr, Appt1, Appt2.....Appt13 --Column A is the list of 3 patients --B2:N4 = Dates of patients future appointments Sheet2 = a list of all patients and the formula to return their last appointment on the books (Max(Date)) --Row 1 is column lables (insignificant): PatientNbr, Last Visit Date --Column A is the list of 3 patients -- In B2, enter this formula: =SUMPRODUCT(MAX((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2 :$N$4))) --Copy this formula down into cells B3 & B4 Now you will have to expand this these ranges so they fit your situation. I used 13 appointments, that equals column N. You will have to expand this out to column U (If your first appointment is in column B). Also, expand it to include all of your patients. Now, if you add patients from time to time or decide to expand this to 25 future appointments, then you will have to readjust your formula each time. But there is a way to make that dynamic if you wanted to. If interested, please write back, but provide more info: Sheet Names and data ranges. HTH, Conan "Dee" wrote in message ... I have a spreadsheet with numerous sheets. In one sheet I have a list of patients who have a number of visit dates. The columns in spreadsheet would have Pt. # Visit Date 1, Visit Date 2 up to maybe 20 visits. I have another sheet in the workbook that has a column named "Last Visit Date". I want to add a formula that will look across the row in the 1st sheet and enter the last date into the "Last Visit Date" in the second sheet. For example if one patient has 20 visits the formula will look across the row and all the visits and if the last visit was visit number 10 it would enter that date into the "last visit date" in the other sheet. I am using Excel 2003. Thank you in advance for any help. Best regards, Dee |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula
Dee,
Everything appears okay, so I'm trying to figure out what is triping this up. I'm wondering if the Sheet name is correct. Verify the spelling of the sheet name. A couple of other things you could try: 1. in the consented patients sheet below the data in a blank row, enter the formula with out the sheet qualifier: --in A185 enter any patien number --in B185 enter this formula: =SUMPRODUCT(MAX(($A$4:$A$181=$A185)*($D$4:$AO$181) )) --verify that the formula is working and returning the correct date. --you can check other patient numbers by changing the number in A185 --you can use any blank row/cells if you already have something in A185:B185, just make sure to change the $A185 reference in the formula. 2. Switch to the Vital status sheet (or any other sheet) and create a formula to a cell on the consented patients sheet --Switch to Vital status (or other sheet) --In a blank cell off to the side, type an equal sign (=) --switch to the consented patients sheet --click any cell. make sure it is only one cell. preferably a cell that has a value or text in it so we can verify that the formula is working (or you can select a blank cell that you are not using and enter your own number/text/date/etc...) --after clicking the cell on the consented patients sheet, hit enter. XL should flip back to the Vital status (other) sheet you were entering the formula on and the value of that cell should be updated to the value/text of the cell on the consented patients sheet it is refering to. --Click this cell and look in the formula bar to see what the formula is. --Copy that formula and past it here, in a reply. I'm checking to see how XL generates the sheet qualifier in the formula, the 'Consented Patients'! part. HTH, Conan "Dee" wrote in message ... Hi Conan, I tried the formula you gave me and received and #Ref error. The workbook I have has the following: in the consented patients sheet the pt numbers are in A4 -A181 and the visits go all the way from D4-AO181 The formula I put in the cell G3 on the Vital status sheet was: =SUMPRODUCT(MAX(('Consented Patients'!$A$4:$A$181=$A4)*('Consented Patients'!$D$4:$AO$181))) Thanks very much for your help. Best regards Dee "Conan Kelly" wrote: Dee, Use the formula I posted earlier. I'll see if I can adjust it for your sheet names, but I can't adjust it to include your whole list of patients This assumes that column labels are in row 1 on both sheets and patient numbers are in column A starting on row 2 in both sheets. Enter the following formula in cell D2 ("Last Visit Date" column) on the "Vital Status" sheet: =SUMPRODUCT(MAX(('Consented Patients'!$A$2:$A$4=$A2)*('Consented Patients'!$B$2:$N$4))) Once again, expand the ranges referenced to include all columns of "Visits" and all rows of patients (my example uses 3 patients & 13 visits). Also, reread my notes at the bottom of my previous post concerning adding new patients over time or expanding to 25 visits instead of 20, and dealing with the expansion dynamically. HTH, Conan "Dee" wrote in message ... Hi Conan, The sheet with the patient number and visit dates is called Consented Patients Pt. # Visit 1 Visit 2 Visit 3 Visit 4 etc. 123 12/10/07 1/15/08 1/25/08 124 9/30/07 11/25/07 The second sheet is named Vital Status Pt. # Site # Country Last Visit Date 123 124 I would like the formula to look in the row for each pt. # and record the last visit date for that patient which for pt 123 would be 1/25/08. Thanks very much for your help, I really appreciate it. Best regards, Dee "Conan Kelly" wrote: Dee, You didn't provide sheet names or data ranges, so... In this example, I used 3 patients and 13 future appointments Sheet1 = a table of all patients and dates of their future visits --Row 1 is column lables (insignificant): PatientNbr, Appt1, Appt2.....Appt13 --Column A is the list of 3 patients --B2:N4 = Dates of patients future appointments Sheet2 = a list of all patients and the formula to return their last appointment on the books (Max(Date)) --Row 1 is column lables (insignificant): PatientNbr, Last Visit Date --Column A is the list of 3 patients -- In B2, enter this formula: =SUMPRODUCT(MAX((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2 :$N$4))) --Copy this formula down into cells B3 & B4 Now you will have to expand this these ranges so they fit your situation. I used 13 appointments, that equals column N. You will have to expand this out to column U (If your first appointment is in column B). Also, expand it to include all of your patients. Now, if you add patients from time to time or decide to expand this to 25 future appointments, then you will have to readjust your formula each time. But there is a way to make that dynamic if you wanted to. If interested, please write back, but provide more info: Sheet Names and data ranges. HTH, Conan "Dee" wrote in message ... I have a spreadsheet with numerous sheets. In one sheet I have a list of patients who have a number of visit dates. The columns in spreadsheet would have Pt. # Visit Date 1, Visit Date 2 up to maybe 20 visits. I have another sheet in the workbook that has a column named "Last Visit Date". I want to add a formula that will look across the row in the 1st sheet and enter the last date into the "Last Visit Date" in the second sheet. For example if one patient has 20 visits the formula will look across the row and all the visits and if the last visit was visit number 10 it would enter that date into the "last visit date" in the other sheet. I am using Excel 2003. Thank you in advance for any help. Best regards, Dee |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula
Hi Conan,
I entered the a patient in A185 and the formula in B185 and got a #value error. I tried a couple of different patient numbers. When I linked the cell in the Vital Status sheet with a cell in the consented patients sheet the formula was ='Consented Patients'!AP4 Thanks again for all your help. Best regards, Dee "Conan Kelly" wrote: Dee, Everything appears okay, so I'm trying to figure out what is triping this up. I'm wondering if the Sheet name is correct. Verify the spelling of the sheet name. A couple of other things you could try: 1. in the consented patients sheet below the data in a blank row, enter the formula with out the sheet qualifier: --in A185 enter any patien number --in B185 enter this formula: =SUMPRODUCT(MAX(($A$4:$A$181=$A185)*($D$4:$AO$181) )) --verify that the formula is working and returning the correct date. --you can check other patient numbers by changing the number in A185 --you can use any blank row/cells if you already have something in A185:B185, just make sure to change the $A185 reference in the formula. 2. Switch to the Vital status sheet (or any other sheet) and create a formula to a cell on the consented patients sheet --Switch to Vital status (or other sheet) --In a blank cell off to the side, type an equal sign (=) --switch to the consented patients sheet --click any cell. make sure it is only one cell. preferably a cell that has a value or text in it so we can verify that the formula is working (or you can select a blank cell that you are not using and enter your own number/text/date/etc...) --after clicking the cell on the consented patients sheet, hit enter. XL should flip back to the Vital status (other) sheet you were entering the formula on and the value of that cell should be updated to the value/text of the cell on the consented patients sheet it is refering to. --Click this cell and look in the formula bar to see what the formula is. --Copy that formula and past it here, in a reply. I'm checking to see how XL generates the sheet qualifier in the formula, the 'Consented Patients'! part. HTH, Conan "Dee" wrote in message ... Hi Conan, I tried the formula you gave me and received and #Ref error. The workbook I have has the following: in the consented patients sheet the pt numbers are in A4 -A181 and the visits go all the way from D4-AO181 The formula I put in the cell G3 on the Vital status sheet was: =SUMPRODUCT(MAX(('Consented Patients'!$A$4:$A$181=$A4)*('Consented Patients'!$D$4:$AO$181))) Thanks very much for your help. Best regards Dee "Conan Kelly" wrote: Dee, Use the formula I posted earlier. I'll see if I can adjust it for your sheet names, but I can't adjust it to include your whole list of patients This assumes that column labels are in row 1 on both sheets and patient numbers are in column A starting on row 2 in both sheets. Enter the following formula in cell D2 ("Last Visit Date" column) on the "Vital Status" sheet: =SUMPRODUCT(MAX(('Consented Patients'!$A$2:$A$4=$A2)*('Consented Patients'!$B$2:$N$4))) Once again, expand the ranges referenced to include all columns of "Visits" and all rows of patients (my example uses 3 patients & 13 visits). Also, reread my notes at the bottom of my previous post concerning adding new patients over time or expanding to 25 visits instead of 20, and dealing with the expansion dynamically. HTH, Conan "Dee" wrote in message ... Hi Conan, The sheet with the patient number and visit dates is called Consented Patients Pt. # Visit 1 Visit 2 Visit 3 Visit 4 etc. 123 12/10/07 1/15/08 1/25/08 124 9/30/07 11/25/07 The second sheet is named Vital Status Pt. # Site # Country Last Visit Date 123 124 I would like the formula to look in the row for each pt. # and record the last visit date for that patient which for pt 123 would be 1/25/08. Thanks very much for your help, I really appreciate it. Best regards, Dee "Conan Kelly" wrote: Dee, You didn't provide sheet names or data ranges, so... In this example, I used 3 patients and 13 future appointments Sheet1 = a table of all patients and dates of their future visits --Row 1 is column lables (insignificant): PatientNbr, Appt1, Appt2.....Appt13 --Column A is the list of 3 patients --B2:N4 = Dates of patients future appointments Sheet2 = a list of all patients and the formula to return their last appointment on the books (Max(Date)) --Row 1 is column lables (insignificant): PatientNbr, Last Visit Date --Column A is the list of 3 patients -- In B2, enter this formula: =SUMPRODUCT(MAX((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2 :$N$4))) --Copy this formula down into cells B3 & B4 Now you will have to expand this these ranges so they fit your situation. I used 13 appointments, that equals column N. You will have to expand this out to column U (If your first appointment is in column B). Also, expand it to include all of your patients. Now, if you add patients from time to time or decide to expand this to 25 future appointments, then you will have to readjust your formula each time. But there is a way to make that dynamic if you wanted to. If interested, please write back, but provide more info: Sheet Names and data ranges. HTH, Conan "Dee" wrote in message ... I have a spreadsheet with numerous sheets. In one sheet I have a list of patients who have a number of visit dates. The columns in spreadsheet would have Pt. # Visit Date 1, Visit Date 2 up to maybe 20 visits. I have another sheet in the workbook that has a column named "Last Visit Date". I want to add a formula that will look across the row in the 1st sheet and enter the last date into the "Last Visit Date" in the second sheet. For example if one patient has 20 visits the formula will look across the row and all the visits and if the last visit was visit number 10 it would enter that date into the "last visit date" in the other sheet. I am using Excel 2003. Thank you in advance for any help. Best regards, Dee |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula
Dee,
I think what that "#value" error means is that in cells D4:AO181 on the Consented Patients sheet, there is data in there that the SUMPRODUCT() function can't handle, like text. The formula I created can only handle numerical data (dates are actually stored as numerical data in XL). In cells D4:AO181, there can only be dates (real dates...not dates stored as text) or blank cells. There can't be any text. Do you know, off hand, if these cells are either blank or real dates? Are other notes/symbols entered into these cells? It looks like we spelled 'Consented Patients'! correctly in my original formula...so maybe that means since we are getting #value when we use the formula on the same sheet, that #value would translate to #ref when we try to use the formula on different sheets. Lets see if we can get rid of the #value on this latest formula first. Once we get this one working correctly, then we can work towards getting it to work on another sheet. HTH, Conan "Dee" wrote in message ... Hi Conan, I entered the a patient in A185 and the formula in B185 and got a #value error. I tried a couple of different patient numbers. When I linked the cell in the Vital Status sheet with a cell in the consented patients sheet the formula was ='Consented Patients'!AP4 Thanks again for all your help. Best regards, Dee "Conan Kelly" wrote: Dee, Everything appears okay, so I'm trying to figure out what is triping this up. I'm wondering if the Sheet name is correct. Verify the spelling of the sheet name. A couple of other things you could try: 1. in the consented patients sheet below the data in a blank row, enter the formula with out the sheet qualifier: --in A185 enter any patien number --in B185 enter this formula: =SUMPRODUCT(MAX(($A$4:$A$181=$A185)*($D$4:$AO$181) )) --verify that the formula is working and returning the correct date. --you can check other patient numbers by changing the number in A185 --you can use any blank row/cells if you already have something in A185:B185, just make sure to change the $A185 reference in the formula. 2. Switch to the Vital status sheet (or any other sheet) and create a formula to a cell on the consented patients sheet --Switch to Vital status (or other sheet) --In a blank cell off to the side, type an equal sign (=) --switch to the consented patients sheet --click any cell. make sure it is only one cell. preferably a cell that has a value or text in it so we can verify that the formula is working (or you can select a blank cell that you are not using and enter your own number/text/date/etc...) --after clicking the cell on the consented patients sheet, hit enter. XL should flip back to the Vital status (other) sheet you were entering the formula on and the value of that cell should be updated to the value/text of the cell on the consented patients sheet it is refering to. --Click this cell and look in the formula bar to see what the formula is. --Copy that formula and past it here, in a reply. I'm checking to see how XL generates the sheet qualifier in the formula, the 'Consented Patients'! part. HTH, Conan "Dee" wrote in message ... Hi Conan, I tried the formula you gave me and received and #Ref error. The workbook I have has the following: in the consented patients sheet the pt numbers are in A4 -A181 and the visits go all the way from D4-AO181 The formula I put in the cell G3 on the Vital status sheet was: =SUMPRODUCT(MAX(('Consented Patients'!$A$4:$A$181=$A4)*('Consented Patients'!$D$4:$AO$181))) Thanks very much for your help. Best regards Dee "Conan Kelly" wrote: Dee, Use the formula I posted earlier. I'll see if I can adjust it for your sheet names, but I can't adjust it to include your whole list of patients This assumes that column labels are in row 1 on both sheets and patient numbers are in column A starting on row 2 in both sheets. Enter the following formula in cell D2 ("Last Visit Date" column) on the "Vital Status" sheet: =SUMPRODUCT(MAX(('Consented Patients'!$A$2:$A$4=$A2)*('Consented Patients'!$B$2:$N$4))) Once again, expand the ranges referenced to include all columns of "Visits" and all rows of patients (my example uses 3 patients & 13 visits). Also, reread my notes at the bottom of my previous post concerning adding new patients over time or expanding to 25 visits instead of 20, and dealing with the expansion dynamically. HTH, Conan "Dee" wrote in message ... Hi Conan, The sheet with the patient number and visit dates is called Consented Patients Pt. # Visit 1 Visit 2 Visit 3 Visit 4 etc. 123 12/10/07 1/15/08 1/25/08 124 9/30/07 11/25/07 The second sheet is named Vital Status Pt. # Site # Country Last Visit Date 123 124 I would like the formula to look in the row for each pt. # and record the last visit date for that patient which for pt 123 would be 1/25/08. Thanks very much for your help, I really appreciate it. Best regards, Dee "Conan Kelly" wrote: Dee, You didn't provide sheet names or data ranges, so... In this example, I used 3 patients and 13 future appointments Sheet1 = a table of all patients and dates of their future visits --Row 1 is column lables (insignificant): PatientNbr, Appt1, Appt2.....Appt13 --Column A is the list of 3 patients --B2:N4 = Dates of patients future appointments Sheet2 = a list of all patients and the formula to return their last appointment on the books (Max(Date)) --Row 1 is column lables (insignificant): PatientNbr, Last Visit Date --Column A is the list of 3 patients -- In B2, enter this formula: =SUMPRODUCT(MAX((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2 :$N$4))) --Copy this formula down into cells B3 & B4 Now you will have to expand this these ranges so they fit your situation. I used 13 appointments, that equals column N. You will have to expand this out to column U (If your first appointment is in column B). Also, expand it to include all of your patients. Now, if you add patients from time to time or decide to expand this to 25 future appointments, then you will have to readjust your formula each time. But there is a way to make that dynamic if you wanted to. If interested, please write back, but provide more info: Sheet Names and data ranges. HTH, Conan "Dee" wrote in message ... I have a spreadsheet with numerous sheets. In one sheet I have a list of patients who have a number of visit dates. The columns in spreadsheet would have Pt. # Visit Date 1, Visit Date 2 up to maybe 20 visits. I have another sheet in the workbook that has a column named "Last Visit Date". I want to add a formula that will look across the row in the 1st sheet and enter the last date into the "Last Visit Date" in the second sheet. For example if one patient has 20 visits the formula will look across the row and all the visits and if the last visit was visit number 10 it would enter that date into the "last visit date" in the other sheet. I am using Excel 2003. Thank you in advance for any help. Best regards, Dee |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula
Hi Conan,
The cells are dates but there are blank cells as some of the visits have not been performed. The visit date is only entered after the vist has been perfomed and there are many visits, weekly, monthly and follow-up. The blank cells are also date cells. The idea for the Vital status sheet is to be able to look and this sheet to see what date the patient was in for their last visit. Thanks again for all your help. Best regards, Dee "Conan Kelly" wrote: Dee, I think what that "#value" error means is that in cells D4:AO181 on the Consented Patients sheet, there is data in there that the SUMPRODUCT() function can't handle, like text. The formula I created can only handle numerical data (dates are actually stored as numerical data in XL). In cells D4:AO181, there can only be dates (real dates...not dates stored as text) or blank cells. There can't be any text. Do you know, off hand, if these cells are either blank or real dates? Are other notes/symbols entered into these cells? It looks like we spelled 'Consented Patients'! correctly in my original formula...so maybe that means since we are getting #value when we use the formula on the same sheet, that #value would translate to #ref when we try to use the formula on different sheets. Lets see if we can get rid of the #value on this latest formula first. Once we get this one working correctly, then we can work towards getting it to work on another sheet. HTH, Conan "Dee" wrote in message ... Hi Conan, I entered the a patient in A185 and the formula in B185 and got a #value error. I tried a couple of different patient numbers. When I linked the cell in the Vital Status sheet with a cell in the consented patients sheet the formula was ='Consented Patients'!AP4 Thanks again for all your help. Best regards, Dee "Conan Kelly" wrote: Dee, Everything appears okay, so I'm trying to figure out what is triping this up. I'm wondering if the Sheet name is correct. Verify the spelling of the sheet name. A couple of other things you could try: 1. in the consented patients sheet below the data in a blank row, enter the formula with out the sheet qualifier: --in A185 enter any patien number --in B185 enter this formula: =SUMPRODUCT(MAX(($A$4:$A$181=$A185)*($D$4:$AO$181) )) --verify that the formula is working and returning the correct date. --you can check other patient numbers by changing the number in A185 --you can use any blank row/cells if you already have something in A185:B185, just make sure to change the $A185 reference in the formula. 2. Switch to the Vital status sheet (or any other sheet) and create a formula to a cell on the consented patients sheet --Switch to Vital status (or other sheet) --In a blank cell off to the side, type an equal sign (=) --switch to the consented patients sheet --click any cell. make sure it is only one cell. preferably a cell that has a value or text in it so we can verify that the formula is working (or you can select a blank cell that you are not using and enter your own number/text/date/etc...) --after clicking the cell on the consented patients sheet, hit enter. XL should flip back to the Vital status (other) sheet you were entering the formula on and the value of that cell should be updated to the value/text of the cell on the consented patients sheet it is refering to. --Click this cell and look in the formula bar to see what the formula is. --Copy that formula and past it here, in a reply. I'm checking to see how XL generates the sheet qualifier in the formula, the 'Consented Patients'! part. HTH, Conan "Dee" wrote in message ... Hi Conan, I tried the formula you gave me and received and #Ref error. The workbook I have has the following: in the consented patients sheet the pt numbers are in A4 -A181 and the visits go all the way from D4-AO181 The formula I put in the cell G3 on the Vital status sheet was: =SUMPRODUCT(MAX(('Consented Patients'!$A$4:$A$181=$A4)*('Consented Patients'!$D$4:$AO$181))) Thanks very much for your help. Best regards Dee "Conan Kelly" wrote: Dee, Use the formula I posted earlier. I'll see if I can adjust it for your sheet names, but I can't adjust it to include your whole list of patients This assumes that column labels are in row 1 on both sheets and patient numbers are in column A starting on row 2 in both sheets. Enter the following formula in cell D2 ("Last Visit Date" column) on the "Vital Status" sheet: =SUMPRODUCT(MAX(('Consented Patients'!$A$2:$A$4=$A2)*('Consented Patients'!$B$2:$N$4))) Once again, expand the ranges referenced to include all columns of "Visits" and all rows of patients (my example uses 3 patients & 13 visits). Also, reread my notes at the bottom of my previous post concerning adding new patients over time or expanding to 25 visits instead of 20, and dealing with the expansion dynamically. HTH, Conan "Dee" wrote in message ... Hi Conan, The sheet with the patient number and visit dates is called Consented Patients Pt. # Visit 1 Visit 2 Visit 3 Visit 4 etc. 123 12/10/07 1/15/08 1/25/08 124 9/30/07 11/25/07 The second sheet is named Vital Status Pt. # Site # Country Last Visit Date 123 124 I would like the formula to look in the row for each pt. # and record the last visit date for that patient which for pt 123 would be 1/25/08. Thanks very much for your help, I really appreciate it. Best regards, Dee "Conan Kelly" wrote: Dee, You didn't provide sheet names or data ranges, so... In this example, I used 3 patients and 13 future appointments Sheet1 = a table of all patients and dates of their future visits --Row 1 is column lables (insignificant): PatientNbr, Appt1, Appt2.....Appt13 --Column A is the list of 3 patients --B2:N4 = Dates of patients future appointments Sheet2 = a list of all patients and the formula to return their last appointment on the books (Max(Date)) --Row 1 is column lables (insignificant): PatientNbr, Last Visit Date --Column A is the list of 3 patients -- In B2, enter this formula: =SUMPRODUCT(MAX((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2 :$N$4))) --Copy this formula down into cells B3 & B4 Now you will have to expand this these ranges so they fit your situation. I used 13 appointments, that equals column N. You will have to expand this out to column U (If your first appointment is in column B). Also, expand it to include all of your patients. Now, if you add patients from time to time or decide to expand this to 25 future appointments, then you will have to readjust your formula each time. But there is a way to make that dynamic if you wanted to. If interested, please write back, but provide more info: Sheet Names and data ranges. HTH, Conan "Dee" wrote in message ... I have a spreadsheet with numerous sheets. In one sheet I have a list of patients who have a number of visit dates. The columns in spreadsheet would have Pt. # Visit Date 1, Visit Date 2 up to maybe 20 visits. I have another sheet in the workbook that has a column named "Last Visit Date". I want to add a formula that will look across the row in the 1st sheet and enter the last date into the "Last Visit Date" in the second sheet. For example if one patient has 20 visits the formula will look across the row and all the visits and if the last visit was visit number 10 it would enter that date into the "last visit date" in the other sheet. I am using Excel 2003. Thank you in advance for any help. Best regards, Dee |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up formula
Dee,
Without being able to work directly with your file, I think there must be some text in one/some of the cells in D4:AO181. If you want, I could look at it to see what is going on. Just make sure you remove all of the confidential info. I would never ask you to send me confidential information. The easiest way to do this might be: --Save As and give it a new name...anything you want. --Make sure you are working in this new copy (I don't want you to loose anything from your original file) --Delete all worksheets except "Consented Patients" --Change Patient Numbers in column A. Just start with "1" and number them consecutively: ----In cell A4, enter a "1" (no quotes) ----In cell A5, enter "=A4+1" (no quotes) ----Copy/fill cell A5 down to cell A181 --Delete confidential data in columns B & C --IF AT ALL POSSIBLE, please do NOT change anything in cells D4:AO181 (hopefully there is no confidential info in these cells). --Make sure to check columns to the right of AO and rows below 181 for confidential data. (I hope you are using XL 2002/2003. If you are using XL 2007, see if you can save it as a file type compatible with prior versions of XL. If not, I might be able to work on it tonight when I get home.) Send me this new file in an email to: CTBarbarin "at" msn "dot" com (replace "at" and "dot" with appropriate symbols). If I can look at this file, I can help you out better. HTH, Conan "Dee" wrote in message ... Hi Conan, The cells are dates but there are blank cells as some of the visits have not been performed. The visit date is only entered after the vist has been perfomed and there are many visits, weekly, monthly and follow-up. The blank cells are also date cells. The idea for the Vital status sheet is to be able to look and this sheet to see what date the patient was in for their last visit. Thanks again for all your help. Best regards, Dee "Conan Kelly" wrote: Dee, I think what that "#value" error means is that in cells D4:AO181 on the Consented Patients sheet, there is data in there that the SUMPRODUCT() function can't handle, like text. The formula I created can only handle numerical data (dates are actually stored as numerical data in XL). In cells D4:AO181, there can only be dates (real dates...not dates stored as text) or blank cells. There can't be any text. Do you know, off hand, if these cells are either blank or real dates? Are other notes/symbols entered into these cells? It looks like we spelled 'Consented Patients'! correctly in my original formula...so maybe that means since we are getting #value when we use the formula on the same sheet, that #value would translate to #ref when we try to use the formula on different sheets. Lets see if we can get rid of the #value on this latest formula first. Once we get this one working correctly, then we can work towards getting it to work on another sheet. HTH, Conan "Dee" wrote in message ... Hi Conan, I entered the a patient in A185 and the formula in B185 and got a #value error. I tried a couple of different patient numbers. When I linked the cell in the Vital Status sheet with a cell in the consented patients sheet the formula was ='Consented Patients'!AP4 Thanks again for all your help. Best regards, Dee "Conan Kelly" wrote: Dee, Everything appears okay, so I'm trying to figure out what is triping this up. I'm wondering if the Sheet name is correct. Verify the spelling of the sheet name. A couple of other things you could try: 1. in the consented patients sheet below the data in a blank row, enter the formula with out the sheet qualifier: --in A185 enter any patien number --in B185 enter this formula: =SUMPRODUCT(MAX(($A$4:$A$181=$A185)*($D$4:$AO$181) )) --verify that the formula is working and returning the correct date. --you can check other patient numbers by changing the number in A185 --you can use any blank row/cells if you already have something in A185:B185, just make sure to change the $A185 reference in the formula. 2. Switch to the Vital status sheet (or any other sheet) and create a formula to a cell on the consented patients sheet --Switch to Vital status (or other sheet) --In a blank cell off to the side, type an equal sign (=) --switch to the consented patients sheet --click any cell. make sure it is only one cell. preferably a cell that has a value or text in it so we can verify that the formula is working (or you can select a blank cell that you are not using and enter your own number/text/date/etc...) --after clicking the cell on the consented patients sheet, hit enter. XL should flip back to the Vital status (other) sheet you were entering the formula on and the value of that cell should be updated to the value/text of the cell on the consented patients sheet it is refering to. --Click this cell and look in the formula bar to see what the formula is. --Copy that formula and past it here, in a reply. I'm checking to see how XL generates the sheet qualifier in the formula, the 'Consented Patients'! part. HTH, Conan "Dee" wrote in message ... Hi Conan, I tried the formula you gave me and received and #Ref error. The workbook I have has the following: in the consented patients sheet the pt numbers are in A4 -A181 and the visits go all the way from D4-AO181 The formula I put in the cell G3 on the Vital status sheet was: =SUMPRODUCT(MAX(('Consented Patients'!$A$4:$A$181=$A4)*('Consented Patients'!$D$4:$AO$181))) Thanks very much for your help. Best regards Dee "Conan Kelly" wrote: Dee, Use the formula I posted earlier. I'll see if I can adjust it for your sheet names, but I can't adjust it to include your whole list of patients This assumes that column labels are in row 1 on both sheets and patient numbers are in column A starting on row 2 in both sheets. Enter the following formula in cell D2 ("Last Visit Date" column) on the "Vital Status" sheet: =SUMPRODUCT(MAX(('Consented Patients'!$A$2:$A$4=$A2)*('Consented Patients'!$B$2:$N$4))) Once again, expand the ranges referenced to include all columns of "Visits" and all rows of patients (my example uses 3 patients & 13 visits). Also, reread my notes at the bottom of my previous post concerning adding new patients over time or expanding to 25 visits instead of 20, and dealing with the expansion dynamically. HTH, Conan "Dee" wrote in message ... Hi Conan, The sheet with the patient number and visit dates is called Consented Patients Pt. # Visit 1 Visit 2 Visit 3 Visit 4 etc. 123 12/10/07 1/15/08 1/25/08 124 9/30/07 11/25/07 The second sheet is named Vital Status Pt. # Site # Country Last Visit Date 123 124 I would like the formula to look in the row for each pt. # and record the last visit date for that patient which for pt 123 would be 1/25/08. Thanks very much for your help, I really appreciate it. Best regards, Dee "Conan Kelly" wrote: Dee, You didn't provide sheet names or data ranges, so... In this example, I used 3 patients and 13 future appointments Sheet1 = a table of all patients and dates of their future visits --Row 1 is column lables (insignificant): PatientNbr, Appt1, Appt2.....Appt13 --Column A is the list of 3 patients --B2:N4 = Dates of patients future appointments Sheet2 = a list of all patients and the formula to return their last appointment on the books (Max(Date)) --Row 1 is column lables (insignificant): PatientNbr, Last Visit Date --Column A is the list of 3 patients -- In B2, enter this formula: =SUMPRODUCT(MAX((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2 :$N$4))) --Copy this formula down into cells B3 & B4 Now you will have to expand this these ranges so they fit your situation. I used 13 appointments, that equals column N. You will have to expand this out to column U (If your first appointment is in column B). Also, expand it to include all of your patients. Now, if you add patients from time to time or decide to expand this to 25 future appointments, then you will have to readjust your formula each time. But there is a way to make that dynamic if you wanted to. If interested, please write back, but provide more info: Sheet Names and data ranges. HTH, Conan "Dee" wrote in message ... I have a spreadsheet with numerous sheets. In one sheet I have a list of patients who have a number of visit dates. The columns in spreadsheet would have Pt. # Visit Date 1, Visit Date 2 up to maybe 20 visits. I have another sheet in the workbook that has a column named "Last Visit Date". I want to add a formula that will look across the row in the 1st sheet and enter the last date into the "Last Visit Date" in the second sheet. For example if one patient has 20 visits the formula will look across the row and all the visits and if the last visit was visit number 10 it would enter that date into the "last visit date" in the other sheet. I am using Excel 2003. Thank you in advance for any help. Best regards, Dee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|