Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / New Users / July 2006

Tip: Looking for answers? Try searching our database.

Automating data entry

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
mrdata - 21 Jul 2006 00:09 GMT
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

Signature

mrdata

Reply to this Message
LFM - 21 Jul 2006 03:21 GMT
use the VLOOKUP function.

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

> Hi
> How can you make excel search for the values you enter in a cell in
[quoted text clipped - 26 lines]
> Thanks
> Charles
Reply to this Message
mrdata - 21 Jul 2006 10:11 GMT
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

Signature

mrdata

Reply to this Message
Ardus Petus - 21 Jul 2006 10:29 GMT
In sheet "main" cell G5, enter:
=VLOOKUP(f5,Jobs!A:B,2,0)

HTH
--
AP

> Hi
> How can you make excel search for the values you enter in a cell in
[quoted text clipped - 26 lines]
> Thanks
> Charles
Reply to this Message
mrdata - 21 Jul 2006 23:10 GMT
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

Signature

mrdata

Reply to this Message
Ardus Petus - 22 Jul 2006 08:51 GMT
Post (as attachment) some sample data

Cheers
--
AP

> In sheet "main" cell G5, enter:
> =VLOOKUP(f5,Jobs!A:B,2,0)
[quoted text clipped - 3 lines]
> regards
> Charles
Reply to this Message
Saruman - 22 Jul 2006 10:01 GMT
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
---------------------------------------------------------------------------

> Hi
> How can you make excel search for the values you enter in a cell in
[quoted text clipped - 32 lines]
> mrdata's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17899
> View this thread: http://www.excelforum.com/showthread.php?threadid=563513
Reply to this Message
mrdata - 22 Jul 2006 14:01 GMT
See attachment

Thanks
Charles

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

Signature

mrdata

Reply to this Message
Ardus Petus - 22 Jul 2006 14:39 GMT
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

> See attachment
>
[quoted text clipped - 5 lines]
> |Download: http://www.excelforum.com/attachment.php?postid=5072     |
> +-------------------------------------------------------------------+
Reply to this Message
mrdata - 22 Jul 2006 17:10 GMT
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

Signature

mrdata

Reply to this Message
Gord Dibben - 22 Jul 2006 18:40 GMT
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

>Mr. Ardus Petus  my hat is off to you! Take a bow!
>
[quoted text clipped - 13 lines]
>Many thanks
>Charles
Reply to this Message
mrdata - 22 Jul 2006 21:34 GMT
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

Signature

mrdata

Reply to this Message
Gord Dibben - 22 Jul 2006 22:00 GMT
=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

>Your formula
>=IF(ISNA(VLOOKUP(C2,$C$1:$F$14,3,FALSE)),"No Job Code
[quoted text clipped - 9 lines]
>
>Charles
Reply to this Message
mrdata - 22 Jul 2006 22:11 GMT
That didn't work either but I fixed it with Conditional formatting.

Thanks Anyway
Charle
Reply to this Message
Gord Dibben - 22 Jul 2006 22:21 GMT
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
Reply to this Message
mrdata - 25 Jul 2006 22:10 GMT
=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

Signature

mrdata

Reply to this Message
Harlan Grove - 26 Jul 2006 02:33 GMT
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))
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.