Auto filling field values
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Auto filling field values

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com