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


--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=563513

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" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> 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
>
>
> --
> mrdata
> ------------------------------------------------------------------------
> mrdata's Profile:
> http://www.excelforum.com/member.php...o&userid=17899
> View this thread: http://www.excelforum.com/showthread...hreadid=563513
>

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


--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=563513

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

HTH
--
AP

"mrdata" <(E-Mail Removed)> a écrit dans
le message de news: (E-Mail Removed)...
>
> 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
>
>
> --
> mrdata
> ------------------------------------------------------------------------
> mrdata's Profile:
> http://www.excelforum.com/member.php...o&userid=17899
> View this thread: http://www.excelforum.com/showthread...hreadid=563513
>

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


--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=563513

Post (as attachment) some sample data

Cheers
--
AP

"mrdata" <(E-Mail Removed)> a écrit dans
le message de news: (E-Mail Removed)...
>
> 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
>
>
> --
> mrdata
> ------------------------------------------------------------------------
> mrdata's Profile:
> http://www.excelforum.com/member.php...o&userid=17899
> View this thread: http://www.excelforum.com/showthread...hreadid=563513
>

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

--
Saruman
---------------------------------------------------------------------------
All Outgoing Mail Scanned By Norton Antivirus 2003
---------------------------------------------------------------------------

"mrdata" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> 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
>
>
> --
> mrdata
> ------------------------------------------------------------------------
> mrdata's Profile:
http://www.excelforum.com/member.php...o&userid=17899
> View this thread: http://www.excelforum.com/showthread...hreadid=563513
>

See attachment

Thanks
Charles


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

--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=563513

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

"mrdata" <(E-Mail Removed)> a écrit dans
le message de news: (E-Mail Removed)...
>
> See attachment
>
> Thanks
> Charles
>
>
> +-------------------------------------------------------------------+
> |Filename: Book2.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=5072 |
> +-------------------------------------------------------------------+
>
> --
> mrdata
> ------------------------------------------------------------------------
> mrdata's Profile:
> http://www.excelforum.com/member.php...o&userid=17899
> View this thread: http://www.excelforum.com/showthread...hreadid=563513
>

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


--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=563513