look up formula

[Dee]

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

 

[Conan Kelly]

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]

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]

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]

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]

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]

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]

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]

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]

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