Transfer specific data onto new sheet

**Johfra** · 05-23-2013, 10:02 AM

Hi,

I have tried many formulas and none seem to work....do what I want.

What I am trying to do is use my patient's list with personal info transferred onto a new sheet but using only specific cells.

Sheet1 will have a list of patient names with their hospital number, address, telephone, medical issue(s), surgery, products needed, etc. BUT, I want to create a medical form for each but individually.

Sheet1 data is entered in by a nurse. I want her to be able to select which patient will need which form and then be able to fill in the blanks.

Being in a hospital, we are short-staffed (like pretty much everywhere) and I want to create this for her to avoid her taking 20 minutes per patient. By having a list, a part-time secretary can then fill in the blanks and save in the correct folder (each patient has their own electronic folder).

On each form (there are 3), I need the name, hospital number, address and phone number. Each form will be for different institutions (government help, certificate of eligibility and product company supply). However, on Sheet1, there is more information for statistical purposes (in all, is about 10 columns).

Can this easily be done or am I dreaming big?

Thanks.

**JBeaucaire** · 05-23-2013, 11:58 AM

Yes, it can be done. The logic steps you will need to accomplish:

1) Create a named range of the names on the sheet1 to make entering the names on the other sheets simpler.

Select Sheet1
Press CTRL-F3 to open the Name Manager
Click NEW
Under NAME use something like PatientNames (notice there is no space)
Under RefersTo: use this dynamic formula that will grab all the names in column A starting at A2:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A)-1)
Click OK to save.... now you have a "list" you can use on other sheets to help enter names

2) On your forms, use this list in the cell where the patient names are to be typed.

Click on the form's Patient Name field
Open the Data > Validation settings for this cell
Use these settings:
- Allow: List
- Source: =PatientNames
Now you can use the drop down list to select a name. You can type free hand, but it will have to match exactly.

3) Now on the rest of your form fields, use VLOOKUP formulas to draw back information from Sheet1 by matching the value from your Patient Name field. Do this on all your forms.