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