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