Automating data entry

[mrdata]

Hi
How can you make excel search for the values you enter in a cell in
another worksheet which contains two columns of data ,Jobcode and Job
title.

I want to be able to key in a jobcode on the main worksheet column
F5:F300

And have excel find the jobcode in the "Jobs" worksheet column "A"
return the corrisponding Job Title from column "B" and enter it in
column G5:G300


Example:

This is the "Jobs" worksheet where the search data will be stored.

Jobcode''''''''''''''''''''Jobtitle
830074''''''''''''''''''''''Creeler


This is the main worksheet
Column F
Row 5 the jobcode is keyed in
Jobcode''''''''''''''''''''Jobtitle
830074 """"""""""" I want this the (Job Title) to be foud and entered
automaticly



Can this be done?

Thanks
Charles

 

[LFM]

use the VLOOKUP function.

You will use the V lookup on F5 to look up column 1 and return the value of
column 2.

 

[mrdata]

I have tried to use vlookup but I can't get it right I don't know what I
am doing wrong If I put the formula in F5 then when I key the jobcode in
F5 I overwrite the formula!

I am really a noob when it comes to formula's and functions unless they
are simple math formula's.

Can you explain how to set this up?

Thanks for responding so quickly.
Charles

 

[Ardus Petus]

In sheet "main" cell G5, enter:
=VLOOKUP(f5,Jobs!A:B,2,0)

HTH

 

[mrdata]

Ardus Petus wrote:
In sheet "main" cell G5, enter:
=VLOOKUP(f5,Jobs!A:B,2,0)

I tried your suggestion And it doesn't work all I get is N/A

regards
Charles

 

[Ardus Petus]

Post (as attachment) some sample data

Cheers

 

[Saruman]

I have experienced problems with a vlookup if the cell formats are not the
same. ie if one cell is formatted as General and the other is formatted as
text, sometimes the lookup gives a N/A error

 

[mrdata]

See attachment

Thanks
Charles


+-------------------------------------------------------------------+
|Filename: Book2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5072 |
+-------------------------------------------------------------------+

 

[Ardus Petus]

Since Job codes in sheet Jobs is text, you must convert search value to
text:
=VLOOKUP(TEXT(F5,0),Jobs!A:B,2,0)

HTH

 

[mrdata]

Mr. Ardus Petus my hat is off to you! Take a bow!

Worked like a charm!

One more thing is it possible to have the cells display a default value
instead of #N/A in the rows that have no jobcode assigned ?
I will copy this formula in column G down about 300 rows.

If I have no jobcode entered in Column F can the formula make the cell
return
"No Jobcode Assigned" instead of #N/A ?

Also when I lock these cells (G5:300)with will the formula's still
work?


Many thanks
Charles

 

[Gord Dibben]

Charles

Example formula for first question.

=IF(ISNA(VLOOKUP(C2,$C$1:$F$14,3,FALSE)),"No Job Code
Assigned",VLOOKUP(C2,$C$1:$F$14,3,FALSE))

Locking the cells will not disable the formulas.


Gord Dibben MS Excel MVP

 

[mrdata]

Your formula
=IF(ISNA(VLOOKUP(C2,$C$1:$F$14,3,FALSE)),"No Job Code
Assigned",VLOOKUP(C2,$C$1:$F$14,3,FALSE))

I can't get it to work with the formula that does work.

=VLOOKUP(TEXT(F5,0),Jobs!A:B,2,0)

I tried to change it to your code but could not get it to work.

Any Idea's

Charles

 

[Gord Dibben]

=IF(ISNA(VLOOKUP(TEXT(F5,0),Jobs!A:B,2,0)),"No Job Code
Assigned",VLOOKUP(TEXT(F5,0),Jobs!A:B,2,0))


Gord

 

[mrdata]

That didn't work either but I fixed it with Conditional formatting.

Thanks Anyway
Charle

 

[Gord Dibben]

Works for me.

Sorry I couldn't help you.


Gord

That didn't work either but I fixed it with Conditional formatting.

Thanks Anyway
Charles

Gord Dibben MS Excel MVP

 

[mrdata]

=VLOOKUP(TEXT(F5,0),Jobs!A:B,2,0)

The formula you gave me above worked great until I added a few more
rows of data to the Jobs worksheet.

Any entry that was added past the original entrys will not lookup.
Did I loose the formula referance?
should I rename the worksheet?

What do I need to do to get this to work again?
It works down to row 50 but after that nothing is returned I check to
make sure all cells were formated the same and I also tried sorting the
list ascending didn't help.

Thanks
Charles

 

[Harlan Grove]

mrdata wrote...

=VLOOKUP(TEXT(F5,0),Jobs!A:B,2,0)

....

If the formula above works, then your cell F5 is a number while
Jobs!A:A is text. If all cells in Jobs!A:A appear as numbers, you'd be
better off ensuring that they are numbers, e.g., by selecting Jobs!A:A
and running Data > Text to Columns, selecting Fixed Width and
immediately clicking Finish. Then you could just use

=VLOOKUP(F5,Jobs!A:B,2,0)

or maybe

=VLOOKUP(ROUND(F5,0),Jobs!A:B,2,0)

If your formula isn't working when Jobs!A:A is text, then it's possible
there are spaces in the entries in Jobs!A:A which you wouldn't be able
to see. That's another good reason to convert Jobs!A:A to numbers if
all cells in it appear to be numbers.

If you have to live with whatever is in Jobs!A:A, but you need to
ignore stray spaces, then you can't use entire column references. If
you don't actually have anything to lookup in row 65536, try

=INDEX(Jobs!B$1:B$65535,MATCH(F5&"",TRIM(SUBSTITUTE(Jobs!A$1:A$65535,
CHAR(160),"")),0))