Hi WildDaisy
To get you started, try the VLOOKUP formula
Create 2 workbooks. Name one ADMIT and the other DISCHARGE
Put these details in the first 4 columns of ADMIT in Sheet1
A B C D
Patient No Name Address Age
1 Jim road 20
2 Fred street 30
3 Pete lane 40
4 Meg avenue 50
Put these details in the first 4 columns of DISCHARGE in Sheet1
A B C D
Patient No Name Address Age
1
2
3
4
In Sheet1 of DISCHARGE, select cell B2 and carry out this procedure
type =VLOOKUP(
Select cell A2 notice in the command line that this has added A2 to your formula
Press F4 on your keyboard 3 times till the A2 in your formula becomes $A2
(the $ before the column A keeps your formula fixed to that column when you scroll it )
type a comma
Select ADMIT, Sheet1 and the whole range from A2 to D5
( notice in the command line this has added your array
as an absolute reference - ie both row and column are preceded by $)
type a comma
type: 2 this designates that you want data returned from column 2 or your array
type a comma
type: False this allows a search of the patient number even if they are not in sequence and returns error is match not found.
type: )
Immediately press enter.
You now have the following formula =VLOOKUP($A2,[Admit.xls]Sheet1!$A$2:$D$5,2) in cell B2
Now Select B2
Put your cursor over the bottom right corner of this cell till the pointer becomes a black cross
Hold down left mouse and drag right to cell D2
Select cell C2 and change the last 2 in the formula to 3 (to get data from column 3). PRESS ENTER
Select cell D2 and change the last 2 in the formula to 4 (to get data from column 4) PRESS ENTER
Select range B2:D2
Put your cursor over the bottom right corner of this range till the pointer becomes a black cross
Hold down left mouse and drag down to cell D5
By using the patient number as a reference you have now obtained data from the ADMIT sheet.
Read the Help file on VLOOKUP and you will soon be an expert.
Good luck
Derek
