format cell to search text in another cell

I'm trying to make it so that when text is typed in a cell, other cells will 
read it, and if the text includes certain letters (like "ss" in "dssl" or 
"ssdl") then it would insert "N/A" in the appropriate cells. i also want it 
to be able to look for more than one thing. Not case sesitive. Below is by no 
means code, but i don't know how else to explain it.

For instance in cell C14 (IF C3 = "*ss" or "*bl1" then "N/A" else " ")

I tried 

=IF(SEARCH("SS", C6,1)>0,"N/A", " ")

But it only worked if it were true, otherwise it errored, and only satisfied 
half my need.
0
Bob4289 (354)
10/24/2005 11:51:07 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
416 Views

Similar Articles

[PageSpeed] 20

For SS only how about

=IF(ISERROR(SEARCH("SS",C6,1))=TRUE," ","N/A")

Can be modified for other searches
-- 
Gary's Student


"Newbie Bob" wrote:

> I'm trying to make it so that when text is typed in a cell, other cells will 
> read it, and if the text includes certain letters (like "ss" in "dssl" or 
> "ssdl") then it would insert "N/A" in the appropriate cells. i also want it 
> to be able to look for more than one thing. Not case sesitive. Below is by no 
> means code, but i don't know how else to explain it.
> 
> For instance in cell C14 (IF C3 = "*ss" or "*bl1" then "N/A" else " ")
> 
> I tried 
> 
> =IF(SEARCH("SS", C6,1)>0,"N/A", " ")
> 
> But it only worked if it were true, otherwise it errored, and only satisfied 
> half my need.
0
GarysStudent (1572)
10/25/2005 12:19:04 AM
Try this:

=IF(OR(ISNUMBER(SEARCH("ss",C3)),ISNUMBER(SEARCH("bl1",C3))),"N/A","")
-- 
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Newbie Bob" <Newbie Bob@discussions.microsoft.com> wrote in message
news:C4AFD97C-0887-4495-80E3-2E64D4FF597E@microsoft.com...
> I'm trying to make it so that when text is typed in a cell, other cells
will
> read it, and if the text includes certain letters (like "ss" in "dssl" or
> "ssdl") then it would insert "N/A" in the appropriate cells. i also want
it
> to be able to look for more than one thing. Not case sesitive. Below is by
no
> means code, but i don't know how else to explain it.
>
> For instance in cell C14 (IF C3 = "*ss" or "*bl1" then "N/A" else " ")
>
> I tried
>
> =IF(SEARCH("SS", C6,1)>0,"N/A", " ")
>
> But it only worked if it were true, otherwise it errored, and only
satisfied
> half my need.

0
ragdyer1 (4060)
10/25/2005 12:23:29 AM
Reply:

Similar Artilces:

How do I delete a text box from an Excel spreadsheet?
Rightclick on it and select cut If the textbox is from the control toolbox toolbar, you may have to click on the designmode icon first. Uncle Binky wrote: -- Dave Peterson ...

Activity report
I'm trying to write a report showing completed activities for users and another for accounts (clients). Boy, is it ugly! In version 1.2, with Crystal Reports, the HTML emails looked just fine -- it would format the HTML with no problem. How do I get the same result with SSRS? (version 2000) Thanks, Karla you mean you are trying to report on the contents of an email activity? If so this cannot be done without some custom solution, its also logged as a bug. ============================== John O'Donnell Microsoft CRM MVP Web - http://www.crowecrm.com Blog - http://www.crowe...

Changing text size
Hi, We enter all our customers in our database in all caps (text) for the names, and addresses. But when you put in the zip code and the database auto finds the town and state it is put in upper, and lower case. Is there a way to change this default to all caps for the auto fill. I am experienced in editing xml. files ( just a note) Any help would be appreciated.....Dennis No, there's no way to change that in the front end, that's something that is happening at display time and is meant to make the data more readable. You could enter a suggestion on Customer Sou...

Changing the Tooltip text in an SDI Toolbar
I've been trying to find a way to change the tootip info for a toolbar. Currently, I've tried getting the toolbar CToolBarCtrl and CToolTipCtrl objects but haven't been able to figure out what to do with them. Any help would be appreciated. Thanks, Ken If you are talking about tooltip info of a particular button on a toolbar, change it in resource editor by double clicking on the button and changing the text that appears at the bottom of the dialog. Text that appears before \n is tootip. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Ken Slight" <kslight@char...

importing multiple text files???
Hi, I am experiencing a big problem. As a part of my analysis for PhD I have to analyze more then 1000 files. The data that I have is in text files. To be more precise, I have 5 different data sets, from different meteo centres, each centre has a data set of 365 files for each day during the year. The format of text files is something as following: Camborne Met. Office WINDS rev 4.1 50.20 -5.30 88 02 01 01 00 00 23 0 29 3 35 07:09 (3.0) 07:09 (3.0) 06:08 (3.0) 284 284 65 65 400 400 23 23 12.5 12.5 1 1500 1500 35 35 400 400 43 90.0 43 74.5 313 74.5 HT SPD DIR Radials... 0.101 9.8 113 0.0...

How to see calculation and heading in same cell.
I would like the cell to perform a calculation and then display the answer as will as a heading. In other words the answer and the heading will appear in the same cell. Perhaps you mean something like ="Heading Name: "&A1+B1 ******************* ~Anne Troy www.OfficeArticles.com "Jeracho" <Jeracho@discussions.microsoft.com> wrote in message news:F34BACB9-E2DA-448A-924D-46A475F98F91@microsoft.com... > I would like the cell to perform a calculation and then display the answer as > will as a heading. In other words the answer and the heading will appear in...

How can I Enable a Check Box based on another fields value?
Hi There, I have a form with a disabled check box. I need to enable it when a certain value ("approved") is selected from a combo box. When I am in Form Design View and I have the Check Box selected the Conditional Formatting menu item on the Format menu is greyed out. I am using Access 2003. Can you tell me what I need to do to make this work? Many thanks, David As you've discovered, Conditional Formatting isn't availabe to checkboxes. Try this: Private Sub Form_Current() If Me.YourComboBox = "Approved" Then YourCheckBox.Enabled = True Else YourChe...

one page in booklet format
How do I tell Publisher 2000 to print just page one and 4 and then pause while i reinsert the paper to print pages 2 and 3? duff wrote: > How do I tell Publisher 2000 to print just page one and 4 and then > pause while i reinsert the paper to print pages 2 and 3? File > Print > Pages 1 to 1 > OK > Print as separate booklet = No. Flip pages. File > Print > Pages 2 to 2 > OK > Print as separate booklet = No. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

Coping part of a cell content into a seperate cell
Hi I have two cells, one containing first and middle name and another one with surname. I want to combine the first name and surname into a separate cell, can you advise how I can just copy the first name and miss out the middle name please?? Thanks Caz Hi, I assume that the midle name is separated by a space from the first name and is in column A and the last name in column B =TRIM(LEFT(A2,FIND(" ",a2)-1))&" "&B2 "Caz H" wrote: > Hi > I have two cells, one containing first and middle name and another one with &g...

Passing Values from One Form to Another Including a Combo Box
Hi, hope someone can help with passing two values from one form to another by way of a command button. I have spent a week on various code taken from this site, but still no luck. Please ... someone help!! The form I am passing values from is called PATIENT HISTORY-Form. On this form, I need to pass a date from a field called DateSFESigned and I also need to pass information collected from a Combo box, Combo91. The command button is called Command119. The form that the values are being passed to is called Personal Habits- Form. Thank you in advance for any help on this matter. Maurita ...

Unread search folder unavailable
Good Aftenoon, Could anybody help tell me why in Outlook 2007 on a Windows 7 machine I get the following error "The Operation Failed. An object could not be found." when clicking on the Unread Mail search folder and also why it is greyed out? This also happens for the Large Mail folder, but NOT the Categorized mail and For Follow-up Folders. Tom Can you create a new search folder with the same functions? See if this helps: http://www.officeforlawyers.com/outlook/sf.html -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://...

Adding a formula to the same cell (H5) on every tab
I have an inventory spreadsheet with 125 tabs. The tabs are numbered 1 through 125. The are identical except for the data below the column headings. If I wanted to put a formula in H5 on every tab, can it be done other than manually opening every tab and typing it? One additional question: If I add a Summary Tab, how could I show the value of a specific cell on each tab without manually entering it? I show the formula I'm using bring B3 to the summary for every tab: A B 1 Unit Value 2 1 ='1'!B3 3 2 ='2'!B3 4 3 ='3'!B3 5 4 ='4'!B3 6 5 ='5'!B3 7...

Copy Distribution List from One Computer to Another
Hi Folks - I have seen various methods for accomplishing this, but none seem to help me. Our company uses Outlook 2003 in a NON Exchange Server environment. I have seen a method for copying distribution lists within a Exchange server environment, but it does not work in our environment. Any ideas? Thanks. Michael Michael <info@homekeyinc.com> wrote: > Hi Folks - I have seen various methods for accomplishing this, but > none seem to help me. Our company uses Outlook 2003 in a NON Exchange > Server environment. I have seen a method for copying distribution > lists with...

sumproduct--counting--zero--blank cells
I'm using these formula to count, =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) ........etc how do i get it so bank cells are excluded from the count. The way it is now, they are counted in the 0 to 10 range... Thanks Jeremy -- Message posted via http://www.officekb.com COUNTBLANK(range) "jeremy via OfficeKB.com" wrote: > I'm using these formula to count, > > =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) > =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) > ........etc > how do...

45 Degree Angled Text & Fill Option
I have Excel 2003 (11.6355.6360) running on XP and I'm wondering if this is a bug or not. If you have text in the first Row and you set it to 45 degrees angled, if you try and fill different cells with different fill colors, sometimes the box will fill angled, othertimes straight up and down. As a simple test, try creating a blank worksheet and make the first 3 columns 35 each in width and 100 in height. The type some text in each of the cells - not too much. Now select all 3 cells and format their alignment to 45 degrees. Now pick each one of the cells and fill each with a different ...

How do I check my email on another computer?
"LalaBobo" <LalaBobo@discussions.microsoft.com> wrote in message news:EADEB55F-1548-4A75-92FF-5D119532C946@microsoft.com... Go to it? <g> -- John Blessing http://www.LbeHelpdesk.com - Help Desk software priced to suit all businesses http://www.room-booking-software.com - Schedule rooms & equipment bookings for your meeting/class over the web. http://www.lbetoolbox.com - Remove Duplicates from MS Outlook, find/replace, send newsletters Is it ISP mail (your ISP will have details) or a web based service like Yahoo or Hotmail? -- Mike Hall MS-MVP Windows ...

Extracting data from one sheet to another
I have a speadsheet with 390 rows and 80 columns. For each column, I wish to copy a range of cells and place them in rows so I can create a separate table for each item represented by the column. For instance, I would like to take the data from the range B10:B15 and place that into a range A1:E1, and then data from range C10:C15 and place into a range G1:K1, D10:D15 into I1:M1 and then B16:B21 into A2:E2, C16:C21 into G2:K2, etc, etc, etc I could do this manually, but it could take a while. I was wondering if there was an easier way to do this. Any help would be greatly appreciated. Thanks...

References omit formatting and return cell address
In two cases of references between worksheets, the formatting from the original cell does not appear in the cell that it is referenced to. Case 1: Worksheet 1, A1 contains a currency formatted number - $2,000 Worksheet 2, A1 references the Workhseet 1, A1 cell using the = sign, yet it returns 2000 (unless I manually reformat the Workksheet 2 cell to Currency Case 2: Worksheet 3, A1 contains an apartment # - e.g. 4 Worksheet 4, A1 references this cell but returns the cell address - Worksheet2,!A1' - rather than the number 4. I tried different formats for the number 4,...

How can I insert a cell reference in a footer (eg for variable foo
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Hi only possible with VBA using an event procedure. e.g. put the following code in your workbook module for cell A1 Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterFooter = wksht.range("A1").value End With Next wkSht End Sub -- Regards Frank Kabel Frankfurt, Ger...

Conditional Formatting based on deadlines #2
Excellent! Thanks! My brain gets confused between what you can use i formulas and what can be used in VBA. Alex Delamain Wrote: > Under conditional formatting set condition 1 > Formula is =+$E2<=NOW()+7 > > Then apply your forma -- madblok ----------------------------------------------------------------------- madbloke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1442 View this thread: http://www.excelforum.com/showthread.php?threadid=27448 NOW() has a date and time component TODAY() has only a date component Ok, incorpo...

CRM 1-2 -> 3.0 Input string was not in a correct format. (upgrade
I've the same error during the upgrade from 1.2 to 3.0. I've tried without any opportunity to make sure not to have est. revenue data but the error is still there during upgrade. It happens during the ImportDefaultDataAction step near the end of the upgarde process. Otherwise 1.2 wasn't giving the error. Any idea? thanks in advance Refering to http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbrowser/en-us/default.mspx?query=input+string&dg=microsoft.public.crm&cat=&lang=en&cr=US&pt=&catlist=8d3f188d-a0E9-40e4-86e2-46508a35c759&...

Text in pivot table limited to 255 characters
I am using a pivot table as an efficient way to aggregate text responses from a large data set. However, the pivot table cuts off the text after the first 255 characters (similar to when you copy a worksheet by using the move/copy option). How can I overcome this? I have tried putting the pivot table on the same sheet as the dataset, but that does not work. I should also note that these pivot tables are then fed into an automated report through a complicated set of VLOOKUPs, etc. The pivot table aggregates several questions and responses from many areas of the datset into one discr...

How do I chart the same data cell on a range of worksheets?
I have the same row of cells on numerous worksheets that I want to chart or consolidate onto another worksheet ? Keith - You need to create a consolidated data range: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Keith wrote: > I have the same row of cells on numerous worksheets that I want to chart or > consolidate onto another worksheet ? ...

Count Text Data
Using 2007 on Vista If I've got text data which in some columns either has data or there is a blank, what formula do I use to count how many cells have text in them per column? Many thanks =COUNTA(A2:A200) will count everything except blanks post back if you have numbers as well that should not be counted -- Regards, Peo Sjoblom <weewillie@anon.com> wrote in message news:lfm764d39m4prld1iiqllpjuen7a1eptoq@4ax.com... > Using 2007 on Vista > > If I've got text data which in some columns either has data or there > is a blank, what formula do I use to count...

[2010] a couple of "search" questions
in the (advanced) search window, two questions: - it is possible to save a customized view setup? I mean columns shown, format, sort, and so on... - in the old 2003 version, in the status (bottom of the window) there was the total number of found items: now it's missing :( can I reactivate it? -- /-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\ -=- -=- -=- -=- -=- -=- -=- -=- - -=- >>>>> http://www.bb2002.it :) <<<<< ............ [ al lavoro ] ........... ...