Help with Excel - autopopulate a long table based on infor from an

[jcpotwor]

Hello,

I have a spreadsheet which shows a long running list of people and dates,
for example:

AAA 1-Jul-2007
BBB 2-Jul-2007

The same person can pop up in the list multiple times as they may have
another date assocaited wth them.

I have a second, summary spreadsheet which includes some specific
information on each person (ie: full name, etc), and I would like to have the
detail included in the long list without having to manually copy and paste.
Is there a formula that can do this? I tried using an IF statement but I got
all mixed up.

Thanks for any and all of your help!
Joe

 

[Pete_UK]

You could use VLOOKUP for this, something like:

=VLOOKUP($A1,Sheet2!$A$1:$F$100,COLUMN(B$1),0)

which assumes that:

your names are in column A of the long sheet, starting in A1;
you have data in Sheet2 occupying A1:F100 with names also in column A;
you want to get data from the second column of Sheet2

If Sheet2 is in a separate workbook you will need to put the filename
before the sheet name, and if that workbook is closed you will also
need to include the full path name.

Put the formula in C1 of the long sheet and copy into D1:G1 if you
want to retrieve data from columns 2 to 6 of Sheet2. Then highlight
these 5 cells and copy down for as many entries as you have in the
long sheet. you could then fix the values with <copy> and Edit | Paste
Special | Values.

Hope this helps.

Pete

 

[jcpotwor]

Hi Pete,

I am almost getting your formula but I keep getting just the header title to
fill in my field. I think I didn't have enough info for you before.

My table 1 tracks patients that take medication. The table has a list of
people by an ID number. I also have a visit date so the same person can be
in the table multiple times if they have multiple visits. What I want to
include here is the dose they are prescribed.

001 | 12-July-2007 | dose
002 | 13-Jul-2007 | dose
003 | 14-Jul-2007 | dose
001 | 15-Jul-2007 | dose

My table 2 is a short table and basically is the "code list" where the
person's dose is revealed (amoung other private info).

001 | 1.0mg
002 | 2.0mg

So I entered your formula, and the output is the header that says "dose". I
am sure I have a very minor thing wrong, but I can't figure it out. Can you
help?

Thanks!
Joe