Can't find a value on sheet1 and input it on sheet 2

**breal33** · 11-27-2007, 10:03 PM

I've searched microsoft, excel sites and read excel books but i just can't seem to get the answer i'm looking for :

If I have 2 sheets names 'User Info' and 'Labels' what i'm looking to do is on the 'Labels' sheet under cell J2 to be able to enter a number, 505 in this case which searches for this number under column B of sheet 'User Info' finds it, looks to the entry in column D (the name Ken) and in turn puts the name into cell J2 in sheet 'Labels'.

The real life use for this is to search a phone extension number in User Info sheet, and use the user name at that extension number and place it in a label on the Label sheet for a reception phone. The Reception phone can have multiple extensions on it made up of any 3 digit number on any where from 3 keys to 12+. So the ability to search for 100-999 is needed.

Thank You very much on advance for any help on this

breal33

**semo** · 11-27-2007, 10:09 PM

you tried using vlookup ? attach yor spreadsheet

**breal33** · 11-27-2007, 10:28 PM

here is the file you requested j2 on the label sheet is where i want to put the formula pls

breal33

**EdMac** · 11-28-2007, 06:16 AM

Hi Breal

See the attached - I think it does what you want.

If there are a greater range of extns and users the lookup table will need to be extended.

let me know how you go.

Ed

**breal33** · 11-28-2007, 09:11 AM

that is the main idea, except where 500 was entered (to the left of your formula) is where i would like to enter the formula so as soon as i type in the number 500 it is replaced with whatever name is in column D of user info, if this can't be done then i can always make a 'rough draft label sheet and have a good draft label sheet where i would place the formula. if this can be done please let me know, otherwise, thank you very much edmac for your time and efforts, it is greatly appreciated

breal33

p.s. is it also possible to be able to enter text and not have the formula return a 'check number' error?

**EdMac** · 11-28-2007, 09:41 AM

Hi,

Yes I wondered about the location of the information. You can't have data entry into a cell that has a formula - It could be done using VBA.

The "check number" message is an error trap in case the data entered is not in the llookup table - if you want to get rid of it just delete the words in the formula but leave the speech marks, it will then return nothing.

e.g.

Please Login or Register  to view this content.

Ed

**breal33** · 11-28-2007, 10:50 AM

Fantastic, ty very much again, one last inquiry, if i were to use text instead of a number can i make it put the text i enter as the entry into the formula cell? for example if i choose to use the word 'Page' or 'Transfer' can i tweak your code to put that text into the cell instead of the blank?

p.s what is VBA?

**EdMac** · 11-28-2007, 11:28 AM

Hi

If you change it to the code below, then any text you enter in J2 will appear in K2

Please Login or Register  to view this content.

VBA is a code that sits behind the spread sheet and does clever things - if you have a look at the programming forum, you will get some idea of what it is about.

http://www.excelforum.com/forumdisplay.php?f=7

Ed