Auto Entry Into Column B

Jim Theisen

New Member
Joined
Mar 17, 2002
Messages
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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"
 
Upvote 0
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
 
Upvote 0
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
:)
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
Just one more suggestion...

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

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

I think it looks nicer too !
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top