MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Auto Fill-In


Posted by Sean on June 08, 2001 8:36 AM

This is hard to explain so I will try my best.

I have a piece of paper which has a Job # for a job and a description for that job.

For example:

Job #1 - Program website

What I want to do is lets say you type "1" into field A1 then in B1 I want "Program website" to automatically appear.

Can someone please help me with this? Thank you very much in advance.

Sean


Posted by IML on June 08, 2001 9:00 AM

You can use vertical look up for this.
First create a list that can be hidden later. The first column has your job number, the second the job name. For the formulas sake, name this JOBLIST in the name box.
Now put the following formula in B1
=VLOOKUP(A1,joblist,2,FALSE)

If you want a cleaner look, you can add this if statement so you don't get an error when nothing is in A1
=IF(A1="","",VLOOKUP(A1,joblist,2,FALSE))

good luck

Posted by Sean on June 08, 2001 9:26 AM

Hey IML,

Thanks alot, it works like a charm!

Thanks again,
Sean

Posted by Sean on June 08, 2001 10:01 AM

I'm sorry IML I am having a problem. What exactly am I supposed to name, "joblist"? Am I supposed to select the entire column in the hidden list and then type "joblist" into the Name Box? Or Am I supposed to select both columns in the hidden list and type "joblist" into the Name Box? or? I keep getting #name? and #n/a error messages.

Posted by IML on June 08, 2001 10:13 AM

You would name both rows joblist or something similar. To avoid names, you could just put the range in by cell name. For example:

=VLOOKUP(A1,$K$1:$L$10,2,FALSE)

where you're list of numbers is in K1:K10 and the corresponding names is in L1:L10. The N/A error means that the formula is not finding a match for the item entered in A1 in your list.

Hope that helps.

Posted by Sean on June 08, 2001 11:05 AM

Thanks IML

Thanks again IML, now it works 100%.

You rule!
Sean