I am helping out a doctor with some research and have on one sheet a list of
data (laid out in columns such as patient number, patient name, date of
birth, date of test, test 1 results, etc) and then in the second sheet I have
a table and a graph to display one patient's results.

I want it so that I can just enter the patient's number in and all the
results for that patient come up. I have tried with the DGET and the VLOOKUP
formulas and am having trouble with both; it is returning seemingly 'random'
results no related to what I am looking up.

What is the best method to do this, and what might I be doing wrong?

I have used formula's like this:
=LOOKUP(B3,TESTS!A:A,TESTS!B:B)
and like this
=DGET('Patient Results'!A2:M158,11,A1:A2)

also there is going to be results added continually to these files so there
isn't a definite range; how do you get around that?

thanks heaps!

Hi

One simple method to do it would be to apply DataFilterAutofilter use
dropdown on Patient Name to see just their records.

If you are wanting that data extracted to a second sheet to further
manipulate, use DataAdvanced Filter.

For excellent guidance on how to use Advance Filter, take a look at
Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html#ExtractWs
--
Regards

Roger Govier


"amethyst" wrote in message
...
I am helping out a doctor with some research and have on one sheet a
list of
data (laid out in columns such as patient number, patient name, date
of
birth, date of test, test 1 results, etc) and then in the second sheet
I have
a table and a graph to display one patient's results.

I want it so that I can just enter the patient's number in and all the
results for that patient come up. I have tried with the DGET and the
VLOOKUP
formulas and am having trouble with both; it is returning seemingly
'random'
results no related to what I am looking up.

What is the best method to do this, and what might I be doing wrong?

I have used formula's like this:
=LOOKUP(B3,TESTS!A:A,TESTS!B:B)
and like this
=DGET('Patient Results'!A2:M158,11,A1:A2)

also there is going to be results added continually to these files so
there
isn't a definite range; how do you get around that?

thanks heaps!

You could try auto filter this will list the details in summary or you could
use pivot table report using the page to select the person.

Otherwise you need to use vlookup as

[cell b2]=vlookup(a2, 'tests'!$A:$M, column(), false)
copy that across to column m.

you could also use data validation list to select the person.

to do this you need to define a range name for the patients (column a only)
as the list of patients is on the other sheet

and then in data validation select list and enter the range name.


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"amethyst" wrote:

I am helping out a doctor with some research and have on one sheet a list of
data (laid out in columns such as patient number, patient name, date of
birth, date of test, test 1 results, etc) and then in the second sheet I have
a table and a graph to display one patient's results.

I want it so that I can just enter the patient's number in and all the
results for that patient come up. I have tried with the DGET and the VLOOKUP
formulas and am having trouble with both; it is returning seemingly 'random'
results no related to what I am looking up.

What is the best method to do this, and what might I be doing wrong?

I have used formula's like this:
=LOOKUP(B3,TESTS!A:A,TESTS!B:B)
and like this
=DGET('Patient Results'!A2:M158,11,A1:A2)

also there is going to be results added continually to these files so there
isn't a definite range; how do you get around that?

thanks heaps!