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
0
6/13/2008 11:32:00 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
440 Views

Similar Articles

[PageSpeed] 14

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
0
dave871 (680)
6/14/2008 2:42:00 AM
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
0
6/14/2008 12:57:02 PM
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.
0
dave871 (680)
6/14/2008 3:36:00 PM
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.
0
6/14/2008 4:55:00 PM
You're welcome. Thanks for the feedback.
Dave.
0
dave871 (680)
6/14/2008 5:11:01 PM
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.
0
6/14/2008 6:10:00 PM
Reply:

Similar Artilces:

referencing javascript function on button event
Hello. I have an asp:Button that in the click event I would like to have it run a javascript function (function ProteusListPrint()). I currently have the following in the click event; btnProteusListPrint.Attributes.Add("onclick", "ProteusListPrint()"); This is not working. Am I missing something or is this not the correct way to reference it in the click event of the button (C#)? Thanks... John JohnE wrote: > Hello. I have an asp:Button that in the click event I would like to have it > run a javascript function (function ProteusListPrint()). ...

how to use XPath function "ms:string-compare" in .NET
Hi, Can anyone point me why the following code is not working for me? Its giving me an error while executing "SelectNodes" ... I have strugled for almost one full day.. I am comaratively new to XSL and XSLT concepts. I appriciate any kind help. Thanks, Bhavin. CODE: ********************************************************************** Private Sub Save() Dim xmldocRoot As New XmlDocument() Dim xmlnodeChildNode As XmlNode Dim xmlnodeAttributeNode As XmlNode xmldocRoot.Load("C:\ACBD.XML") 'Dim xsltcon As Xml.Xsl.XsltContext Dim ns As New XmlNamespaceManager(xml...

Need Help with Sumif Function including dates
Hi, I need some assistance with tracking my commissions that are due to be paid each month. The "Comm Due Date" is in column "C", rows 3 - 30 which list by date all of the Commissions Due to be paid and the "Comm Balance" is in column "H", rows 3 - 30 which has the Commission Balance due for each sale. I tried utilizing a formula from a post that I found from April '05 but for some reason I keep receiving a #NAME? error. I'm not sure what I am doing wrong. I want to find the total due by month so that as I add additional sale...

Error 1004 using array in Sheet select.
If I select a sheet with its real name: Sheets("Sheet1").Select >>>>>- Works fine Sheets(Array("Sheet1", "Sheet2")).Select >>>>>- Works fine If I assign the sheet name(s) to a constant Public Const TSGSheet1 = "Sheet1" Public Const TSGSheet2 = "Sheet2" Sheets(TSGSheet1).Select >>>>>- Works fine Sheets(TSGSheet2).Select >>>>>- Works fine Sheets(Array(TSGSheet1, TSGSheet2)).Select >>>>>- fails with Error 1004 My macro's/V...

Text box
I want to add a text box for a email address at the begining of my all my text boxes. The Email address must be inserted into the 1st text box before anyother text box can be completed. I am using MS FrontPage 2003. If you are using the standard FrontPage Form Field validation, the best you can do is to require that they enter something in the field before the form is submitted. Right-click the email one-line text box and select 'Form Field Validation'. Check the 'Data Length' 'Required' checkbox. -- ~ Kathleen Anderson Microsoft MVP - Expression...

How to download & use old product key
I bought my version of Office 2007 in Nov. 2008. New laptop came today. I still have the old product key, but can't access my purchase history on the website. How can I download & install Office 2007 on this new computer with my old product key? I'm ONLY going to be using this new laptop from here on out. Did you keep all the downloaded files on the old laptop? Do you have the original e-mail with the download instructions? Have you tried contacting Digital River? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intac...

using outlookweb access
Hi, i don't know if any one can help me on this. Here it is. I am using microsoft Outlook webaccess to access my e- mail from the office while i am at home. But i also want to be able to browse my company's network at home also, and have access to files and folders,so that i can do work from home. Is this possible. Please resond, this is very urgent. They could set up VPN for you... "Anna" <annam@patnt.com> wrote in message news:04ed01c3a391$ed4a70d0$a001280a@phx.gbl... > Hi, i don't know if any one can help me on this. Here it > is. I am using micros...

Why is it defaulting to plain text for replies and forwards?
All of a sudden, my Outlook is set whereby all replies and forwards open in plain text, even though when I compose a new e-mail it opens in HTML. ...

COUNTA function in VB
I can use the COUNTA function successfully within my worksheet to get the desired answer but I am having trouble using it in VB even though the help file says it can be used in macros. Any help would be greatly appreciated. TIA Phillip This is from an armature but I was having trouble using VLookup in macros and learned here that the syntax is: Application.WorksheetFunction.VLookup(...... Suspect that is the solution to your problem. Ed "Phillip Topping" <ptopping@tpg.com.au> wrote in message news:u6Nf1WfYDHA.1004@TK2MSFTNGP12.phx.gbl... > I can use the COUNTA fun...

Numbers are automatically rounding off
When I type in data in my form, the numbers are rounding off like 42.36 will round off to 42... How do I change this Check the data type of the bound field. If you select Number, the default is Long Integer, which doesn't support decimals. You need to use a data type that does support decimals, such as Single, Double or Decimal. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jtidwell" <jtidwell@triplettking.com> wrote in message news:uj5IaFIyHHA.3588@TK2MSFTNGP06.phx.gbl... > When I type in data in my form, the numbers are ...

Converting variable text strings to numeric
I've imported some word tables into excel, the 'numbers' have spaces instead of commas and consequently are read as text. The 'numbers' vary from single integer to millions For example 1 101 335 293 207 23 113 101 19 While I can use the left, mid and right functions for the millions that formula is unworkable for other numbers. Any ideas, or do I have to get a VB programmer to write a function for me? Hi! Try using Find/Replace Select the range Goto Edit>Replace Find What: enter a space by hitting the space bar Replace With: nothing...

"Thickening" text
I am using Access 2003. I created a form for my database to edit/enter new data. Within the form, I inserted a tab control box with three pages, and within each tab page, there are various fields and labels. My problem happens when I open the form in Form View and scroll through the records. As I scroll, the text in the labels gradually "thickens", to the point where it appears to be in bold and is difficult to read. What is strange about this problem is that 1) it only occurs on the second and third tab pages (not the first), 2) it only happens with True Type fonts in ...

use of Staionery for replies and forwards
I am using the "stationery" function rather than the "signature" function to put a header and a Sincerely Yours in my outgoing emails. That way the spell checker still functions. Problem is that it doesn't come up in when I reply or forward an email. I would like it to Help please... You cannot have the Stationary to be functioning while you reply o forward to messages. This is because the Stationary tends to be th background and so for a forward or a reply you cannot have tha functionality SudharsonA -----------------------------------------------------------------...

Modify RM HATB by Document Number
Hi, Our receivable orders either start with SL which means sales or MN which means maintenance or RN which means renewal. we want to run the HATB separately for each of these order sequence. I tried to add a restriction but do not know how to add a 'begins with' restriction as I only see equal to operator available. Also, say if the restriction works, how can I duplicate the report into 3 copies and run it with different restrictions in the same reports dictionary. Stacey Try using the SUBSTRING function, e.g. SUBSTRING=("SL*") See Knowledge Base article: 856861 ...

convert number format to time format
I have a whole column in which I need to be able to enter a number an it should convert it to the time format. If I enter 100 it shoul convert it to 1:00. Similarly entering 2300 should automatically b converted to 23:00. Thank yo -- Message posted from http://www.ExcelForum.com Hi Fullymooned! No need to send the same question to different groups. The answer given in programming was: See: Chip Pearson: http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax...

number formats that allow you to format the appearance of negativ.
I don't understand what it means to List two number formats that allow you to format the appearance of negative numbers using parentheses? Jasmon There are up to four parts to a format in Excel, separated by ;, so FormatForPositive;FormatForNegative;FormatForZero;FormatForText So if you want two decimals and parenthesis for negatives, you could use 0.00;(0.00);0.00 Notice you don't need one for text so I have not included it. If you miss a format type then you should still include it's separator 0.00;;0.00 Has only set a format for positive numbers and zeroes. You could...

Auto Invoice numbering
I need a auto invoice numbering on a new worksheet that can add a consequence invoice number from the previous worksheet number. Any help is greatly appreciated. Hi see your reply in Excel.programming P.S.: please don't multipost -- Regards Frank Kabel Frankfurt, Germany "unkown" <jshim168@hotmail.com> schrieb im Newsbeitrag news:Ohscc.35941$Ig.13976@pd7tw2no... > I need a auto invoice numbering on a new worksheet that can add a > consequence invoice number from the previous worksheet number. > > Any help is greatly appreciated. > > For the benefi...

How to use Outlook express
hi, is it possible to set up outlook exp. but not allow it to be the default mail client? i'd like to 'check' the link on a page that says: 'contact' and have that email address come up in outlook exp., then copy/paste the address to my own AT&T mail service but not use outlook express for anything else. as of now, i have outlook exp. disconnected altogether and do not wish to use it other than to open up a link. all responses on this are welcome......... thanx, jim Dear, This is an Outlook News group not Outlook Express - since you asked , here is the info for th...

User defined function calls over linked servers
We are running SQL Server 2008 SP2 Build 3282 and we have have tried using a user defined function over a linked server. We did not have success getting the message: "Remote table-valued function calls are not allowed." We have looked into this and found that UDF calls are not allowed over linked servers. Are there any workarounds for this? Use a stored procedure instead, or bring the data from the remote server over to a local table before calling the table-value function against that local data. If multiple users are at play, add a GUID to the row of data ...

[vba-newbie] testing cell text for specific characters?
I would like to test the text value of a cell to determine if i includes a certain string of characters and set a variable to true whe appicable. In other words, something like this (unknown method in human syntax an italics) : -If Range["A1"] text string includes 'thisText' Then- boolVariableName = True End If Is there a built -in way to do this with VBA? I'm sure there is, but haven't been able to locate the appropriate funtion in the help files this forum, or via google. Can anyone assist -- Message posted from http://www.ExcelForum.com One way: boolVar...

How to change column titles from numbers to letters.
The columns are labeled in numbers. How do I change this to letters. mdb60575 schreef: > The columns are labeled in numbers. How do I change this to letters. Tools > Options > General > switch off R1C1 reference style Bart Snel Tools>Options>General Deselect R1C1 reference style -- ERR229 "mdb60575" wrote: > The columns are labeled in numbers. How do I change this to letters. Hi, Go to Tools>Options>General>unselect "R1C1 reference style" HT -- Case ----------------------------------------------------------------------- Casey'...

How do I set up a text insert?
Hi We are fairly new outlook users running acting agency. We want to know how you set something up so that when emailing appointments to people we can just insert the following text TIME: PLACE: DATE: Good luck! I was shown how to do this but have since forgotton. Can anyone help?? Thanks You can use templates for this such as Quick Parts or AutoText. For details see; http://www.howto-outlook.com/howto/messagetemplates.htm -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, A...

Different Number on Printing
I am trying to set up an excel file for my work orders. When a customer comes in I want to open my excel file and have a number show up at the top of document that is like from 1001-1100. But I do not want it to be random, I want it to go in order every time i open the document or better yet print the document. So if I open the document and fill it out and print it it will show 1001 at he top. then when I open it again and print it again it will be at 1002, all the way to infinity. Neemers, I think this may be what you are looking for. http://snipurl.com/db53 Barbara "Neemer...

Importing number format problem in Excel
I am trying to import numbers from a table in Access that is stored as a number format into Excel and Excel does not recognize the numbers as numbers. I have to manually convert the numbers format via the Excel error checking tool everytime. I have this problem when trying to paste any type of number format in Excel. How can I change the default settings in Excel to recognize number formats and automatically paste them in as numbers? I am using Excel XP in case it matters. Thanks Debra Dalgleish has posted a couple of techniques for fixing this after the fact: http://groups.go...

using Entourage 2008 but keeping Word/Excel/PowerPoint 2004
My Exchange host (Intermedia) offers a free download of Entourage 2008 with its small business hosted Exchange package. I am running Office 2004 for now. If I download just the Entourage application, will this work even though the other Office applications are 2004? Should I do anything with the Microsoft User Data folder or something else to prevent file conflicts? If there's any chance of an issue I'll probably just get the full 2008 suite so it's all consistent, but just wanted to know. Thanks. --Russ, NYC "uwsrjk@yahoo.com" wrote: > My Exchange host (Intermedia) ...