Filling in Text from one list to another 1

[Jeanie]

I have a list of doctors and their patients. I also have a form I need to fill out for each doctor with the patients' names and dates of birth. I only want to send one form to each doctor. I'm sure there is a formula, I'm just not sure how to do it.

I have the doctor's names at the top of each form so I want to say if the doctor on the top of the form equals the doctor in column D on the list, then put the name from column A on the list into field B on the form. But, there are several patients per doctor.

Can anyone help?

Thanks!

Jeanie

 

[macropod]

Which application are you using?

 

[Jeanie]

Oops.......sorry about that - I'm using Excel.

 

[macropod]

Hi Jeanie,

Suppose your data are in three Columns: B (Patient Name), C (Patient D.O.B.), D (Doctor), with Row 1 as a heading row, and you've defined a name for each of these columns: Patient, D.O.B. and Doctor, respectively. Suppose also that your form is on a separate worksheet, with the doctor's name in cell A1, and a 'Patient' heading in A2 and a 'D.O.B.' heading in B2.

Given the above conditions, you could put the following formulae in:

A3: =INDEX(Patient,MATCH($A1,Doctor,0))
B3: =INDEX(D.O.B.,MATCH($A$1,Doctor,0))
A4: =IF(OR(A3="",COUNTA(A$3:A3)=COUNTIF(Doctor,$A$1)),"",INDEX(Book1!Patient,MATCH($A$1,OFFSET(Doctor,MATCH(A3,Book1!Patient,0),0,COUNT(Doctor)+1-MATCH(A3,Book1!Patient,0),1),0)+MATCH(A3,Book1!Patient,0)))
B4: =IF(OR(B3="",COUNTA(B$3:B3)=COUNTIF(Doctor,$A$1)),"",INDEX(D.O.B.,MATCH($A$1,OFFSET(Doctor,MATCH(B3,D.O.B.,0),0,COUNT(Doctor)+1-MATCH(B3,D.O.B.,0),1),0)+MATCH(B3,D.O.B.,0)))

Now simply copy the formulae in A4 & B4 down as far as needed to accommodate the maximum number of patients per doctor.

Cheers
PS: 'Missing' D.O.B. data will result in errors, including a D.O.B. output of 0 Jan 1900 for the 1st instance and #N/A errors for all subsequent records.

 

[Jeanie]

Thanks for the solution...and the very complete way you explained it!

Works great.

Jeanie