Auto filling field values

[sutonimh]

Hello,
I'm relativly new to the whole Excel thing. But, currently I am trying to use it as an employee log. The woorkbook I am using has multiple shhets with different information on each. I would like to auto fill some information on one page into fields on another, based on a value i provide.

Example:

I have a list of names and extensions #'s on one sheet. The second page is blank. When I type in a name on the second page I want excel to autofill the extension # into the next field.


Is any of this possible? If so how do I go about it? Also, is it possible to do this across different workbooks?

 

[Derek]

Hello Sutonimh
This is an answer I gave to another poster but the problem is basically the same. Using this procedure the formula will pick up the names of your own workbooks/sheets


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 of 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
This message was edited by Derek on 2002-03-11 23:21

 

[Dave Hawley]

Hi sutonimh

If you hold down your Ctrl key you can click nominated sheet name tabs and 'Group them' This will mean any data entered on one sheet will be automatically entered on the others. Just dont forget to 'un-group' them