Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Using a function with numbers & text

I have 6 columns, B has numbers + text (3d, 6c, ad, kd) C has numbers + text
( 3 down, 6 cube, alternate dialog) I want to input into D 6c and have the
return in E be 6 cube, or ad input/alternate dialog output.
I have over 60 input/output codes.
I tried the IF function but it won't work with text. Is there a function
that will accomplish this?
TIA
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,388
Default Using a function with numbers & text

Hi,
If you have over 60 I/O codes, you should probably use VLOOKUP.
If it doesn't already exist, write a 2-column table of input/output codes.
In C1, enter =IF(D1="","",VLOOKUP(D1,AA1:AB60,2,0))
(Where AA1:AB60 is your table - change to suit)
Then copy down.


"Cardslinger" wrote:

I have 6 columns, B has numbers + text (3d, 6c, ad, kd) C has numbers + text
( 3 down, 6 cube, alternate dialog) I want to input into D 6c and have the
return in E be 6 cube, or ad input/alternate dialog output.
I have over 60 input/output codes.
I tried the IF function but it won't work with text. Is there a function
that will accomplish this?
TIA

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Using a function with numbers & text

Dave
Thanks for the response, I used your code and - AA1:AB60 had to be changed
to A1:B60 or it gave an error.
Even with the changes when I copy down the code increases the input (D1) as
it should but it also increases the A1:B60 also. (A2:B61 etc). I tried
inserting $ ! to the A1:B60 code to make those constant but that wont work.
Anyway to do this?

BTW my name is Dave, good name!!!
Thanks

"Dave" wrote:

Hi,
If you have over 60 I/O codes, you should probably use VLOOKUP.
If it doesn't already exist, write a 2-column table of input/output codes.
In C1, enter =IF(D1="","",VLOOKUP(D1,AA1:AB60,2,0))
(Where AA1:AB60 is your table - change to suit)
Then copy down.


"Cardslinger" wrote:

I have 6 columns, B has numbers + text (3d, 6c, ad, kd) C has numbers + text
( 3 down, 6 cube, alternate dialog) I want to input into D 6c and have the
return in E be 6 cube, or ad input/alternate dialog output.
I have over 60 input/output codes.
I tried the IF function but it won't work with text. Is there a function
that will accomplish this?
TIA

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,388
Default Using a function with numbers & text

Hi,
Does that mean your table range is A1:B60?

Sorry about missing out the $'s. Try this, and copy down.
=IF($D1="","",VLOOKUP($D1,$A$1:$B$60,2,0))

The $A$1:$B$60 will lock the table reference, and the $D1 will lock the
column, but allow the row to increment.

Regards - Dave.
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Using a function with numbers & text

That code worked thanks so much for the help
Happy Fathers Day
Dave

"Dave" wrote:

Hi,
Does that mean your table range is A1:B60?

Sorry about missing out the $'s. Try this, and copy down.
=IF($D1="","",VLOOKUP($D1,$A$1:$B$60,2,0))

The $A$1:$B$60 will lock the table reference, and the $D1 will lock the
column, but allow the row to increment.

Regards - Dave.



  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,388
Default Using a function with numbers & text

You're welcome. Thanks for the feedback.
Dave.
  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Using a function with numbers & text

One more quick question if you don't mind?
As I only have to input 2 charactors is there a way to format the column to
automatically jump to the next cell as soon as i input the 2nd charactor??
That would save time and as i'm doing readouts and inputs I wont forget to
hit tab/return.

"Dave" wrote:

You're welcome. Thanks for the feedback.
Dave.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating function using numbers & text lucy Excel Worksheet Functions 4 June 10th 08 10:45 PM
does an IF function only look up numbers & not text? CAPGirl Excel Discussion (Misc queries) 2 October 26th 06 08:20 PM
function to convert whole numbers to written text Mani K Excel Worksheet Functions 2 December 29th 05 10:24 AM
How do I set up an IF function with text and numbers? Natalie Excel Worksheet Functions 7 November 9th 05 09:50 PM
numbers to text function John T. Sheedy Excel Worksheet Functions 2 March 8th 05 03:13 PM


All times are GMT +1. The time now is 06:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"