Automating data entry

  • Thread starter Thread starter mrdata
  • Start date Start date
M

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
 
use the VLOOKUP function.

You will use the V lookup on F5 to look up column 1 and return the value of
column 2.
 
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 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
 
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
 
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
 
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
 
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
 
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
 
=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
 
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
Click to expand...

Gord Dibben MS Excel MVP
 
=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
 
mrdata wrote...
=VLOOKUP(TEXT(F5,0),Jobs!A:B,2,0)
Click to expand...
....

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with Offset 3
selct and Copy data from differant rows at the same time 2
Stuck - Median and Quartiles 3
Median and Quartiles 3
Select value from combo boxes frm multi tables and store in diff t 1
Attempting to design database around Excel datasheet design 6
Correct design and primary keys for linking multiple tables 4
two queries from same data in table into one query 2

Back
Top