Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Auto Entry Into Column B

  1. #1

    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have two columns on a spreadsheet, Column 'A' is for a name and column 'B' is for a number. Each name has a corresponding number which I have to look up everytime I type in a name into column 'A'. I want to be able to type a specific name into column 'A' and have the corresponding number automatically insert itself into column 'B' next to the name. Can anyone help me with this?

    [ This Message was edited by: Jim Theisen on 2002-03-18 02:30 ]

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

    Default

    Hi Jim

    I assume you want a Worksheet Function for this. I also assume your numbers are names in Column A and B are in a different sheet to the name you enter into Column "A"

    In Column B of the Lookup sheet put:
    =VLOOKUP(A1,Sheet1!A1:B1000,2,FALSE)


    I think you may also be interested in this very user friedly method I have for this here: http://www.ozgrid.com/download/default.htm
    Under: "Lookup.zip"




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

    Default

    Hi

    If all names you need are already in Column A, then, assuming your last entry in column B is in row 19, put this formula in B20 under your last entry and scroll down
    =VLOOKUP(A20,A:B,2,FALSE)
    Change A20 to suite your actual row.

    regards
    Derek

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-18 02:44, Dave Hawley wrote:
    Hi Jim

    I assume you want a Worksheet Function for this. I also assume your numbers are names in Column A and B are in a different sheet to the name you enter into Column "A"

    In Column B of the Lookup sheet put:
    =VLOOKUP(A1,Sheet1!A1:B1000,2,FALSE)


    I think you may also be interested in this very user friedly method I have for this here: http://www.ozgrid.com/download/default.htm
    Under: "Lookup.zip"
    Dave,

    don't you need to anchor that range if you're "putting" it in ?

    I know it anchors automatically if you're doing it with the mouse, but not if you're just typing it

    (caught myself out many times with that !)

    Chris



  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris

    BTW worth remember anchor only if dragging else worthless, as you say mouse does thsi auto.

    Also anchor only as you need that is is drag by rows anchor columns only and v's'v else it goes pear shaped.

    Funnyhow it works, also woth eremembering one Vlookup i thinng is not a formula but combine 3 will remove them n/a and so on. I know these are important but to me blanks are professional and to acheive this 3 vlookup with if are reuired.

    Just some points..

    Anchor as needed not carte blanc!
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

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

    Default

    Hi Guys


    Your right it should be "anchored". That's what happens when you type straight in the message board I guess. I am just so used to having any ranges named (normally dynamic) that I often forget this.



  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just one more suggestion...

    You can replace the FALSE with a 0 (To avoid 4 extra types... )

    =VLOOKUP(A1,Sheet1!$A$1:$B$1000,2,0)

    I think it looks nicer too !
    Regards,

    Juan Pablo González
    http://www.juanpg.com

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

    Default

    Hmm, I don't agree with the use of zeros in place of False, or any number in place of True. It can makes a spreadsheet very hard to trouble-shoot and is asking for trouble if you use Edit>Replace on zeros!. There is no benefit in using zero over False (other than 4 less key strokes) but shorter can often mean less efficient.

    JMHO

Some videos you may like

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
  •