Auto Entry Into Column B

Jim Theisen · Mar 18th, 2002, 07:30 AM

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 ]

Dave Hawley · Mar 18th, 2002, 07:44 AM

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"

Derek · Mar 18th, 2002, 07:52 AM

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

Chris Davison · Mar 19th, 2002, 04:39 PM

Quote:

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

Jack in the UK · Mar 19th, 2002, 09:21 PM

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!

Dave Hawley · Mar 19th, 2002, 10:37 PM

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.

Juan Pablo González · Mar 20th, 2002, 12:53 AM

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 !

Dave Hawley · Mar 20th, 2002, 06:14 AM

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

Mar 19th, 2002, 09:21 PM	#5
Jack in the UK Join Date: Feb 2002 Posts: 3,063	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"

Mar 20th, 2002, 12:53 AM	#7
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,758	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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 18th, 2002, 07:30 AM	#1
Jim Theisen Join Date: Mar 2002 Posts: 3	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 ]

Mar 18th, 2002, 07:44 AM	#2
Dave Hawley Banned Join Date: Mar 2002 Posts: 1,582	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"

Mar 18th, 2002, 07:52 AM	#3
Derek Join Date: Feb 2002 Location: Perth Australia Posts: 1,564	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

Mar 19th, 2002, 10:37 PM	#6
Dave Hawley Banned Join Date: Mar 2002 Posts: 1,582	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.

Mar 20th, 2002, 06:14 AM	#8
Dave Hawley Banned Join Date: Mar 2002 Posts: 1,582	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