Help with a reference list to automatically generate details

[Sindagon]

Dear All,

This is my first post on this forum. I have racked my brains trying to come up with an adequate solution but I haven't been able to find one that works so far. My problem is as follows:

I have a database which includes a list of people's names, surnames and I.D. numbers (in that order, in colums K, L and M respectively). These are on a sheet named "EMTP". These entries I just mentioned can be repeated in more than one row. On another sheet named "Patient List" I have the reference list of individual people's names, surnames and I.D. numbers, in columns A, B and C respectively, which I want to use as a reference. I somehow want to link the information in the reference list to the main information on the "EMTP" sheet in a way that will allow me to input the I.D. number only (in column M), and have the associated information (name and surname) pop up automatically in the adjacent cells in columns K and L, IF the patient is already in my "reference list". If nothing pops up, I will know it is a new one so I will need to be able to insert it into my reference list for future use.

Would anyone be able to help me with this because I'm at my wit's end.

Kindest Regards,
Sindagon

 

[SteveO59L]

have a look at vlookup()

 

[Sindagon]

Believe me I've tried. I even had a friend try to help me. He came up with an INDEX and MATCH formula but I haven't been able to get the cell references to match

The formula he suggested is as follows: =INDEX('Patient List'!$A$1:$C$999,MATCH($A11,'Patient List'!$A$1:$A$999,0),MATCH(EMTP!B$1,EMTP!$A$1:$C$1,0))

Tried replacing the references etc but nothing's working. I would think I would need two different formulas for columns K and L but I'm not sure. I'm out of ideas and shamelessly asking whether anyone would be able to write the formulas for me because the proble is way above my skill level.

 

[SteveO59L]

ok, in your "Patient List" sheet you will need ID as the first column.

in column n of your "EMPT" sheet,

=VLOOKUP(M2,'Patient List'!A:C,2,FALSE)

 

[Sindagon]

Thanks, gonna try it now.

 

[Sindagon]

Ah, can't have it in column N as it is hidden and in use in another formula that generates a code for the I.D.

 

[SteveO59L]

apologies, should be in col L

=VLOOKUP(k2,'Patient List'!A:C,2,FALSE)

 

[Sindagon]

None required. Ah, still doesn't work. So in the Patients List Sheet I put the ID as the leftmost column. Still can't understand how to make it work :/ My sheets now look like the following:

"EMTP" sheet

K L M
Name Surname I.D.

"Patients List" sheet

A B C
I.D. Name Surname
123M John

 

[Sindagon]

None required. Ah, still doesn't work. So in the Patients List Sheet I put the ID as the leftmost column. Still can't understand how to make it work :/ My sheets now look like the following:

I want to be able to input just the I.D. numbers into column M, so inputting 123M in column M would give me John in column K same row, and Doe in column N same row.

"EMTP" sheet[TABLE="width: 500"]
<tbody>[TR]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Surname[/TD]
[TD]I.D.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

"Patient List" sheet[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]I.D.[/TD]
[TD]Name[/TD]
[TD]Surname[/TD]
[/TR]
[TR]
[TD]123M[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]555L[/TD]
[TD]Mary[/TD]
[TD]Wilkinson[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sindagon]

I'm not sure but I would assume that the formula would have to link column's K, L and M for K and L to take information from M, which inturn would be linked to columns A, B and C on the other sheet.