What command can I use to do the following ...

[hnts]

I have an Excel Workbook with 2 worsheets labeled 1) Medicare Hospitalized List and a second worksheet labeled 2) Hospitals.

The first worksheet includes 7 columnar fields labeled A)Patient Name, B)Entry Date, C)Hospital, D)Phone Number, E)Room Number, F)Status and G)Discharge Date.

The second worksheet has a list of all the available hospitals in the area and includes the following data: A)Number, B)Name and C)Telphone #.

I would like to know what function to use to have the first worksheet look at the second and plug in that data. Example ... The person entering the data into the first worksheet enters name, and entry date adn then looks to sheet # 2 and enters the coresponding name and number of the hospital.

How is that done? Any input would be helpful.

Thanks

Rudy

 

[Ainapo3]

Believe the function would either be VLOOKUP or HLOOKUP [vertical lookup or horizontal lookup] depending on how you need to search the corresponding worksheet. It is a bit tricky to set up, but it works great.

 

[bluedragon2]

=vlookup(<sheet1 criteria>,<sheet2 Table>,<Table column>,0)

<sheet1 criteria> is the reference on sheet 1 that will search the table on sheet 2

<sheet2 Table> is the table of sheet two.

<Table column> is the number of the column for the data you want.

Example Data:

Sheet 1:

Col A Col B Col C Col D ColC
Pat Name Date Hosp# Hosp Name Hosp Tel#

Sheet 2:

Col A Col B Col C
Hosp# Hosp Name Hosp Tel#


On sheet 1 Cell D2:
=VLOOKUP(C2,sheet2!A:C,2,0)
and Cell E2:
=VLOOKUP(C2,sheet2!A:C,3,0)

If you want blanks if nothing is found then:

=IF(ISNA(VLOOKUP(C2,sheet2!A:C,2,0)),&quot;&quot;,VLOOKUP(C2,sheet2!A:C,2,0))



Blue

If I wasn't Blue, I would just be a Dragon...