referencing text in an IF statement

I am trying to use an IF statement to look for text in range of cells but 
cannot get it to return the value I want I think because I'm searching for 
text not a value.  If any cell in a column contains the text "Error" I want 
the cell I'm in to also read "Error".  Here's the formula I have but it won't 
work: =IF(OR(A122:A457="Error"),"Error","").  Any suggestions?

0
Utf
12/4/2009 5:09:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
771 Views

Similar Articles

[PageSpeed] 38

Hi,
try

=if(COUNTIF(a122:a457,"Error")>0,"Error","")

"Michelle Thompson" wrote:

> I am trying to use an IF statement to look for text in range of cells but 
> cannot get it to return the value I want I think because I'm searching for 
> text not a value.  If any cell in a column contains the text "Error" I want 
> the cell I'm in to also read "Error".  Here's the formula I have but it won't 
> work: =IF(OR(A122:A457="Error"),"Error","").  Any suggestions?
> 
0
Utf
12/4/2009 5:33:01 PM
*ARRAY-ENTERED*:
=IF(SUM(--(NOT(ISERR(SEARCH("Error",A122:A457)))))>0,"Error","All OK")


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=159638

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

0
p45cal
12/4/2009 5:38:45 PM
thanks so much they both worked!

"p45cal" wrote:

> 
> *ARRAY-ENTERED*:
> =IF(SUM(--(NOT(ISERR(SEARCH("Error",A122:A457)))))>0,"Error","All OK")
> 
> 
> -- 
> p45cal
> 
> *p45cal*
> ------------------------------------------------------------------------
> p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=159638
> 
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> 
> .
> 
0
Utf
12/4/2009 6:41:01 PM
Reply:

Similar Artilces:

Vanishing Entourage Message Text
OK, so here's a good one. On the MacBook Pro, running latest OS and up to date Office 2004, some email messages display no text. Toggling the "display complex HTML" does not resolve this. If I get the same message forwarded through another user, it often displays properly. Suggestions? Dan Hi Dan, are you by any chance connected to an Exchange 2003 server? If yes, Microsoft released a patch a week or two ago which introduced a bug in OWA. They are aware of the problem and working on a fix. If that does not explain your problem, you'll have to post back and be more sp...

Referencing another field if result is N/A
Hello, I have a situation where I am referencing data on another workbook. The reason behind this is the data in Column H can be entered incorrectly and often is. I am able to prevent this going forward but can not do so for the items already entered. I enter a formula via VBA, however, sometimes the result will be #N/A and if so I would prefer to leave the old value and just highlight it that it needs to be verified by hand. I am not sure if I can just "leave" the value as it has pretty much already been replaced. Due to this, I copy the column to a different colum...

Multiple SEARCHes within same TEXT string
Hi, I have a column which contains text strings that represent colours. However, the *actual* colour is often buried in other text i.e. METBLACKPAINT. I have a list of 12 single-word colours i.e. BLACK, and want to identify in one formula which (if any) of the 12 is buried in the text string (and then have the formula return that colour) If I had just 7 colours, I'd just go with nested IFs and SEARCH, but as I have 12 that is not an option. Any ideas would be greatly appreciated. Many thanks, Jason Assume your 12 single-word colours eg: BLACK, WHITE, etc are in a defined co...

Importing multiple text files to spreadsheet #2
Hi Nikos or whomever, Thaks for the code. I tried it out, & it managed to import the 1st fil & then give the following error w/ 3 available buttons...... -------------------------------------------------------- Run-time error '1004' Application-defined or object-defined error >>> [End] [Debug] [Help] -------------------------------------------------------- If i go into [Debug], I get the following line highlighted i yellow.... -> ActiveCell.Offset(i, j).Value = vLine As I'm not a VB programmer, I will try my best to figure this one ou using the help &...

copy text on the screen as text?
A long time ago I wrote a program that copied the screen. But that produced an image of the screen. I'm guessing that that is the best one can do but I would like to copy text on the screen as text (i.e. as a string) Do you know if that is possible? Or maybe, you know why that it is not possible - if so, of course, I'd like to know that. Thanks Cal Who wrote: > A long time ago I wrote a program that copied the screen. > But that produced an image of the screen. > > I'm guessing that that is the best one can do but I would like to copy text...

Excel Text Function
Hi anyone who can help me... I have some info in a spreadsheet as follows: A1 B1 C1 Centra Dublin Centra Belfast Centra London If I want to get just Centra out into another cell I would use =LEFT(A1:C1,6) and this works fine. But I want to actually get out the area - Dublin, Belfast or London and some other areas that might have more or less than 7 letters. Any ideas??? Thanks in advance Ann (Dublin, Ireland) =TRIM(SUBSTITUTE(A1,"Centra","")) will work if you have city names and centra.. -- Regards, Peo Sjoblom "Ann&q...

add accents to text
I want to add accents to first/last names in a spreadsheet I'd also like to be able to add scientific symbols to text in a spreadsheet. From your START button go to Programs/Accessories/System Tools/Character Map Slow way: use Select/Copy then Paste into Excel Faster way: click on the accented letter you want, look at bottom right corner (eg for ï {as in naïve} you'll see "Keystroke: Alt + 0239). Do this for all the accented letters you think you will need. Keep the paper handy and use the Alt + four digit code to obtain your results. Stephen Powell "mtambeau...

Referencing merged cells
I'm having trouble referencing a merged cell in another workbook. Say I merge cells A1 to C1 in workbook 1. When I make this the active cell, the Name Box says "A1". When I go to another workbook (say, workbook 2), go to cell B1, type "=" and then go to the merged cell and select it, I get 'workbook1'!$A$1:$C$1 as the formula and, of course, the "#VALUE" error. I've successfully tried typing "=sum(" without the quotes hen clicking on the contents of the cell and then adding the ")" and it works O.K. but there should be an easi...

Numbers are text-need to add them
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...

If statement
I have been trying to create my first if statement without success please could someone put me out of my misery. I have two columns with figures in and in column 3 I wish to enter the difference if stock on hand is less than stock needed but if the stock is more than needed I wish to enter zero Example Stock needed stock on hand Order 100 25 75(the formula would calculate difference and enter 75) 300 301 currently my formula is entering -1 but I want it to enter 0 TIA =IF(A1>A2,A1-A2,0) and just copy down -- HTH RP (remove nother...

Glyphs in Hoefler Text Ornaments font do not display in Office Programs
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am having difficulty getting characters from Hoefler Text Ornaments, an Apple system-supplied TrueType font (unicode based, version 6.1d4e1; 2008-09-16), to display or print in most Office programs (Entourage excluded--it types and cuts and pastes there). While the font functions perfectly in Office X versions, it does not in both Office 2004 and 2008 programs, Entourage exempt in the latter two. <br><br>According to Apple, the characters are glyphs, and are accessible in the Mac character palate ...

Worksheet doesn't permit F4 absolute referencing tool in formulas
I've never seen this before in a worksheet and can't find a Tool-Option that would change it. You can of course type the dollar signs in, but the F4 key won't toggle. Sure it's something simple, any help appreciated! -- ronthedog ------------------------------------------------------------------------ ronthedog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26504 View this thread: http://www.excelforum.com/showthread.php?threadid=397725 ...

This workbook is currently referenced by another workbook and cannot be closed.
The message is given when I try to close a workbook / kill Microsoft Excel. I'm using Microsoft Excel 2002 / XP. The spreadsheet includes a reference to another sheet that someone else referenced in another message here. I put the AddIn sheet in via: Tools, AddIns..., Analysis ToolPack - VBA. I've not referenced any function or procedure in the add-in. I get a Triangular Warning / Error ? It's got a graphic of a Yellow Triangle with an Exclamation Mark inside. The subject is the exact text of the warning. A Microsoft Search on the subject yielded nothing. Google yielded one ...

Wrapping Text in Excel
I have tried the following techniques to wrap text in excel. I have followed them exactly as shown and in that order. The process works fine in a workbook. When I move to another workbook the Wrap text style is gone from the Style box. How can I make it stay permanently for all workbooks? Adding a Shortcut to Excel for Wrapping Text Step 1: Adding the Style Box to the Formatting Toolbar: 1. Right-click one of the toolbars, and select Customize. 2. Select the Commands tab, and then select Format. 3. Drag the Style icon from the Customize dialog box to the Formatting toolbar, and drop it n...

Formula Changes when data entered in referenced range
I have 5 formulas in a worksheet that refer to a range. When data is entered in the range, the formulas adjust to exclude the cells with data. Range B5:B211 When data is entered in B5, range in the formulas becomes B6:B212. The workbook has nearly 200 worksheets with the same formulas. I'm working in Excel 2003. Does anybody have any idea what's going on????? Might help if you include the formulae that you are using. Regards Trevor "mac849" <mac849@discussions.microsoft.com> wrote in message news:33BDD2CD-C071-4DAE-A821-4967E94C16A3@microsoft.com... >...

Shapes/Text
How can i make words in the shape of a heart (for example) I want my words to illustrate a shape. Please help. You really can't, Publisher can add text to a shape, but only as a rectangle. If you have patience I have a method on my web page that shows how to do text in shape of a heart. Takes some work. Scroll way to the bottom of the page. http://msauer.mvps.org/more_graphics.htm -- Mary Sauer http://msauer.mvps.org/ "ShapeUser" <ShapeUser@discussions.microsoft.com> wrote in message news:CABE22F7-82BF-40C9-8125-09E088C090C6@microsoft.com... ...

list of emails into text file
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Is anyone aware of a way I can copy a selection of emails from my inbox and paste into a text file or new email body. <br><br>So if my inbox shows 10 mails, I'd select all 10, copy, and paste into a file the subjects, date and times of each mail. <br><br>This would be great, and I'm sure a script is possible but I've no idea where to start. <br><br>Thanks in advance! On 2/26/10 1:25 AM, Smallbadger@officeformac.com wrote: > Is anyone aware of a way I can copy a se...

Transparent text boxes
I have text boxes which have a fill color yet transparent center and then an image behind the text box. On the screen it shows up perfectly, yet when I print it the image is blocked. Any ideas? Thank You Publisher has issues with transparency. Create a rectangle the size of your text box, apply your transparency, right-click, save as picture, select the .png format, re-insert the saved image into your publication. -- Mary Sauer http://msauer.mvps.org/ "Sam@Postnet" <Sam@Postnet@discussions.microsoft.com> wrote in message news:2C9504A9-C26E-4025-8320-405ADE655DBC@mic...

Take the space out of text?????
I have exported some information into a excel spreadsheet that I will be importing into another program. Unfortunately one of the colums has a space between the text that I need to remove. Example: how cell looks now: abcde fghij how I need it to look: abcdefghij Unfortunately it is not a small worksheet. It has 5863 rows that need to be changed. PLEASE!!!! PLEASE!!!! HELP!!!!!! If anyone can help I would greatly appreciate it!!!!!!!!! Thank you, Nichole --- Message posted from http://www.ExcelForum.com/ Hi you have already posted this. Have you read the answers ?? -- Regards Frank...

Enter Text and Formula in same cell
Is it possible to enter text "Total is" and also enter the formula to total the column in the same cell? If so, how? "Sherrell" <swheeler@fpc.cc.tx.us> wrote in message news:5d1a499a.0312080833.48d5068@posting.google.com... > Is it possible to enter text "Total is" and also enter the formula to > total the column in the same cell? If so, how? Something such as: ="Total is "&SUM(A1:A10) Sherrell Try this: ="Total is �"&SUM(D11:D14) Andy. "Sherrell" <swheeler@fpc.cc.tx.us> wrote in message news:5d1a...

referencing the cell above
Hi I have a kin a formula wich gets the value from the cell above and then add some. What I get is an list of values incrementing. When I delete one row in this list, all cells below 'crashes' with #REF!. What I need is that for the formulas to always look one cell up, even if I delete a row. Now it seems that if I delete Row8, then when Row9 become Row8 it reference to it self and then ov course goes bananas... Hope I made myself understandable ;-) Any ideas folks? -------- stuhag --------- Instead of using say =A19, use =OFFSET(A20,-1,0). This would go in A20, and as it does...

Indirect Range Referencing
Hello, I work in a paper mill. I am building a spreadsheet with several charts. I have a wide sheet of paper coming off of a paper machine of varying widths. This paper is wound up on a "reel".I have a scanner that measures the weight of the paper on the reel, and each scan gives me a 600 point array. I can calculate where the edges of the paper lie within the array, no problem, and plot the whole thing very nicely. The problem is that the paper then goes into a re-winder, where the wide sheet is cut into smaller sheets, that make up "rolls". I can calculate the sta...

Search from value to value on text fields
I have a suggestion concerning search on text fields. Fx the zipcode field. In Denmark we often want to search for an interval fx 5000 to 5999. This isn't possible on Text fields in CRM. You have to create a search with a lot of OR's in AX it is possible to use the syntax 5000..5999. Or a selection of method Between or From and To. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this li...

How can i jump to a cell referenced in a formula?
Anyone know of a way to jump to a cell referenced in a formula, when it is not the first reference...e.g. Formula bar reads "=D5+G5" CTRL+{ will take me to D5 but is there a way to get to G5 easily? Especially helpful in long formulas and off sheet links. thanks Ctrl-Shift-{ than press tab. HTH, Bernie MS Excel MVP "PowerExcelWannabe" <PowerExcelWannabe@discussions.microsoft.com> wrote in message news:88A4B2CC-C90E-4918-8DB7-04C09B6F72AC@microsoft.com... > Anyone know of a way to jump to a cell referenced in a formula, when it is > not the first referen...

Problem with referencing a hidden column
The code below works great, that is until I hide column Q. I do not want the data in column Q to me visible on the worksheet. Is there any way around this problem? TextBox100.Value = Range("Q4").Text TextBox200.Value = Range("Q49").Text TextBox300.Value = Range("Q94").Text TextBox400.Value = Range("Q139").Text TextBox500.Value = Range("Q184").Text TextBox600.Value = Range("Q229").Text TextBox100.Value = Range("Q4") -- Regards Dave Hawley www.ozgrid.com "ordnance1" <ordnance1@comcast...