insert text conditionally and format

Hi,

I want to conditionally format a cell so that it turns red based on 
another's value. Actually, if any one of 3 cells are out of a specified 
value, then this 4th cell should turn. This much I have accomplished.

Is there any way to insert text also? I'd like to have the 4th cell say 
Rejected and turn red if any of the 3 previous cells are out of spec.

To illustrate (anything over 3 is out of spec for the first cell, 4 for the 
2nd, and 2 for the 3rd.)

|   4   |   3   |   1   |   Rejected   |

The 4 would throw off the entire thing. Now my only problem is that I don't 
know how to "conditionally insert text" as opposed to format. I'm pretty sure 
that excel is inflexible like this and I will have to write a macro or 
something, which is completely useless for the people who are going to be 
using this.

Thank you for any advice in advance,
John
0
8/17/2005 4:00:20 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
496 Views

Similar Articles

[PageSpeed] 23

Let's say you three conditions are in cols A,B,C and let's say that over 3 is 
out of spec, then in column D:

=if((A1<3)*(b1<3)*(C1<3),"","Rejected")
will give you the result.
-- 
Gary's Student


"John Sawyer" wrote:

> Hi,
> 
> I want to conditionally format a cell so that it turns red based on 
> another's value. Actually, if any one of 3 cells are out of a specified 
> value, then this 4th cell should turn. This much I have accomplished.
> 
> Is there any way to insert text also? I'd like to have the 4th cell say 
> Rejected and turn red if any of the 3 previous cells are out of spec.
> 
> To illustrate (anything over 3 is out of spec for the first cell, 4 for the 
> 2nd, and 2 for the 3rd.)
> 
> |   4   |   3   |   1   |   Rejected   |
> 
> The 4 would throw off the entire thing. Now my only problem is that I don't 
> know how to "conditionally insert text" as opposed to format. I'm pretty sure 
> that excel is inflexible like this and I will have to write a macro or 
> something, which is completely useless for the people who are going to be 
> using this.
> 
> Thank you for any advice in advance,
> John
0
GarysStudent (1572)
8/17/2005 4:41:02 PM
=IF((F145>98)*(G145>44)*(H145>10),"","Rejected")

I get absolutely nothing from this. The reason I am using these numbers is 
so that I can copy it directly from the table to attempt to show what is 
going wrong. This is the formula directly from the table. I don't get colors 
or the word regardless of what I enter. Any idea?

"Gary's Student" wrote:

> Let's say you three conditions are in cols A,B,C and let's say that over 3 is 
> out of spec, then in column D:
> 
> =if((A1<3)*(b1<3)*(C1<3),"","Rejected")
> will give you the result.
> -- 
> Gary's Student
> 
> 
> "John Sawyer" wrote:
> 
> > Hi,
> > 
> > I want to conditionally format a cell so that it turns red based on 
> > another's value. Actually, if any one of 3 cells are out of a specified 
> > value, then this 4th cell should turn. This much I have accomplished.
> > 
> > Is there any way to insert text also? I'd like to have the 4th cell say 
> > Rejected and turn red if any of the 3 previous cells are out of spec.
> > 
> > To illustrate (anything over 3 is out of spec for the first cell, 4 for the 
> > 2nd, and 2 for the 3rd.)
> > 
> > |   4   |   3   |   1   |   Rejected   |
> > 
> > The 4 would throw off the entire thing. Now my only problem is that I don't 
> > know how to "conditionally insert text" as opposed to format. I'm pretty sure 
> > that excel is inflexible like this and I will have to write a macro or 
> > something, which is completely useless for the people who are going to be 
> > using this.
> > 
> > Thank you for any advice in advance,
> > John
0
8/17/2005 5:47:15 PM
Nevermind, I think I've figured it out, and thank you for your help

"John Sawyer" wrote:

> =IF((F145>98)*(G145>44)*(H145>10),"","Rejected")
> 
> I get absolutely nothing from this. The reason I am using these numbers is 
> so that I can copy it directly from the table to attempt to show what is 
> going wrong. This is the formula directly from the table. I don't get colors 
> or the word regardless of what I enter. Any idea?
> 
> "Gary's Student" wrote:
> 
> > Let's say you three conditions are in cols A,B,C and let's say that over 3 is 
> > out of spec, then in column D:
> > 
> > =if((A1<3)*(b1<3)*(C1<3),"","Rejected")
> > will give you the result.
> > -- 
> > Gary's Student
> > 
> > 
> > "John Sawyer" wrote:
> > 
> > > Hi,
> > > 
> > > I want to conditionally format a cell so that it turns red based on 
> > > another's value. Actually, if any one of 3 cells are out of a specified 
> > > value, then this 4th cell should turn. This much I have accomplished.
> > > 
> > > Is there any way to insert text also? I'd like to have the 4th cell say 
> > > Rejected and turn red if any of the 3 previous cells are out of spec.
> > > 
> > > To illustrate (anything over 3 is out of spec for the first cell, 4 for the 
> > > 2nd, and 2 for the 3rd.)
> > > 
> > > |   4   |   3   |   1   |   Rejected   |
> > > 
> > > The 4 would throw off the entire thing. Now my only problem is that I don't 
> > > know how to "conditionally insert text" as opposed to format. I'm pretty sure 
> > > that excel is inflexible like this and I will have to write a macro or 
> > > something, which is completely useless for the people who are going to be 
> > > using this.
> > > 
> > > Thank you for any advice in advance,
> > > John
0
8/17/2005 5:51:01 PM
Reply:

Similar Artilces:

How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman Numerlas in my document? "ckj" <ckj@discussions.microsoft.com> wrote in message news:6CF0C580-BA6E-463D-B4FD-E6A6FDD9E814@microsoft.com... > New to Word 2007 would someone please explain to me how to insert Roman > Numerlas in my document? Just type them... I II III IV V VI VII VIII IX X XI etc etc. Type a number e.g. 21, select it and run the following macro Dim oRng As Range Set oRng = Selection.Range oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...

Auto correct text changes
I am editing a document in word 2007 that has been saved in compatible mode. Whe I try to turn one heading into italics or bold it changes all the headings in the document to the same formatiing. Can any one help me to stop this happening. If I then press undo all the other changes revert back and not the one I have just made. I can't keep pressing undo everytime I make a change. I just want it to change the words I am highlighting See http://word.mvps.org/faqs/formatting/wholedocumentreformatted.htm. -- Stefan Blom Microsoft Word MVP "David Rudman"...

Insert trigger
Looking for some advice on SQL 2005. I have a table that will usually be populated by an SSIS package. I want to set the "loaddate" column to the current time after a record is inserted. Should i do this via trigger or should i just build a step in the SSIS package to update the column after the file loads? If trigger is the way to go, what is the syntax to create the after insert trigger? Thanks in advance. You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date assignment without any need for coding. -...

colour formatting
hi all.. Has anyone any idea how to chnge the colour of the text in say a1, a2, a3 simply from the results of what word is placed in cell a4 TIA Use the Format-->Conditional formatting command. 1. Select cells A1:A3 2. Click Format-->Conditional formatting 3. Change the first dropdown (on the left) to "Formula Is" 4. Enter the following formula in the textbox to the right of "Formula Is": =$A$4="Hello" Where: $A$4 = the cell you're testing "Hello" = the word/phrase that will make the font color change. Make sure ...

inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex- "40:15" is there a way to make it more like this...40hrs,15mins -- Message posted from http://www.ExcelForum.com Use a custom format hh"hrs",mm"mins" -- Regards, Peo Sjoblom "-Brian-H- >" <<Brian-H-.110wgs@excelforum-nospam.com> wrote in message news:Brian-H-.110wgs@excelforum-nospam.com... > I have a cell in my time card that displays total weekly time -ex- > "40:15" is there a way to make it more like this...40hrs,15mins ? > > > ...

how do I filter on text format in Excel?
I have a workbook containing text that I wish to filter based on the format of some words. IOW, for instance, all records (rows) with cells containing text of a certain font color, or perhaps characters with the strikethrough mark. I'm relatively familiar with advanced filtering, but this appears to be something requiring a "filter by formula". Does anyone have any experience in this area? It is not in any built in function in Excel only way this would be possible would be to use a UDF (a function written using VBA) in a help column and then filtger on that help column pl...

insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then save it was as a web page, but I want to keep the excel part interactive. Any ideas? ...

conditional formulating
Hi, like to know if there's other ways besides validating the data changes by colours for conditional formating. Looking for audio validation..ie having the spreadsheet alerting me by making a sound when there's changes in the data. Having pop up alerts will do. THe main purpose is that I do not want to keep on monitoring the spreadsheet all the time to capture the changes in data. The data are linked real time to websites, ie Foreign exchange rates. use msgbox and it'll pop up if you are running a macro which goe through the data. really need more info to be more help :...

Automatic conditional formatting from a list
Hi All I need a macro that colours cells if the product name in column A on it's row matches any in the product list. There is a list of product names in C17:End(xlUp).Row, that have different backgrounds applied to each cell in the list. The macro looks at Column A in the worksheet & compares it to the product name list. Where it finds a product name match, it goes across to columns I:BJ (on the matched row) and applies the product name's background colour to any "active cells" (i.e. value >0). The following code does this but it's applies...

Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options" On making new-email with Attachment-File(s), ** File Menu | Insert | File >>> (Brows and select File to insert ) then we can see the button "Insert", and write side Drop Down Arrow lower-right side of Dialog Box; If it clicked, we can see three options as follows: ** Insert Insert as text Insert as Attachment I can not recognize/understand the difference between "Insert" and "Insert as Attachment" *** What is the difference between Insert and Insert As Attachment ? I would appreciate y...

Inserting Hyperlinks in a Protected Sheet
Hi I run Excel 2000 and I have a protected worksheet that I share wit users in my organisation. I want to allow the insertion of a hyperlin to a specific file type within a specified directory on our server. 3 Questions: 1.Protection on disables the insert hyperlink command. Can this b overcome with worksheet activate code? 2.Can I limit the types of files (preferably by requiring the file t meet a mask format eg "z-*.xls")? 3.Can I limit the directory that can be linked, by referring to pathname stored in a cell on the active sheet? Would appreciate your suggestions. Thanks S...

how do I format text as strikethrough in Publisher 2003?
I am copying over text from a WordPerfect document into Microsoft Publisher 2003. Text that has been formatted as "Strikethrough" in the WordPerfect document comes over as plain text with no strikethrough. I have even tried to just import the document which it looks like it does fairly well at, except that all text formatted as strikethrough is imported as plain text. I can possibly believe that Publisher does not have any way to format text in this way, but I have looked everywhere and cannot figure out how to format the text in this way. Can anyone help? You will have to ...

Insert with a where condition
Hi, sql 2005 I have an insert statement that is ignoring the where condition. That is, I want to insert records when they do not already exist in the destination table. INSERT INTO dbo.tblmnuGroupPerm ( gId ,mtfID ,... ) SELECT @gID ,mtfID ,... FROM dbo.locmnuTabFunction AS ltf WHERE ltf.mtfID NOT IN ( SELECT gp.mtfID FROM dbo.tblmnuGroupPerm AS gp WHERE gp.gId=@gID AND gp.Deleted=0 ) Any ideas or recommendations appreciated :-) Many thanks, Jonathan It's OK... <oops "redFace">I did not correctly se...

Insert
I want to overtype in a Publisher text box. I find I can only insert. The "insert" key doesn't do anything. ...

Conditional Format Percentage
Hi, How can I conditionally format a cell based on the percentage value of another cell rather than the value of its own cell?. All values ar Example, I wish to format "A1" based on the value of B1. If B1 = -5% then A1 would colour Amber, if B1 = -10% then A1 would colour Red, if B1 => 0 then A1 would go Green. Three format to apply - (1) B1 => 0% [Green] (2) B1 <= minus 9.99% [Red] (3) B1 between minus 0.01% & -9.99% [Amber] Can this be done, possibly in one formula for conditional formatting? Thanks, Mickey In the Conditional Formatting dialog, change &quo...

how to insert data in a table
Hi Exprets; I am creating an access database in which I want to insert data in already created table. Kindly help. Regards, Vikky Vikky <love.excel@gmail.com> wrote in news:1194124711.012302.269990 @e34g2000pro.googlegroups.com: > Hi Exprets; > > I am creating an access database in which I want to insert data in > already created table. > > Kindly help. > > Regards, > > Vikky > Data from where? Do you want to import it from excel, from a text file, copy it from another table or type it in manually? -- Bob Quintal PA is y I've altere...

Conditional Formating, contain space
Hi All, How do I HIGHLIGHT a block of data that CONTAIN space?? For an Example, COLOUMN A1-A1000 contains TEXT data. I would like to highlight those cells that contains data. Thanks in advance. New Rule>Use a formula. =NOT(ISBLANK(cellref)) Gord Dibben MS Excel MVP On Wed, 3 Feb 2010 15:51:01 -0800, apache007 <apache007@discussions.microsoft.com> wrote: >Hi All, > >How do I HIGHLIGHT a block of data that CONTAIN space?? > >For an Example, COLOUMN A1-A1000 contains TEXT data. >I would like to highlight those cells that contains data....

How do you insert page numbers larger than 1000?
I have my purchase orders set up as a Publisher document. When our organization upgraded from Publisher 2000 to Publisher 2002, the new version set parameters on the page numbers. This was one of those things that worked just fine in the previous version... Does anyone know how to turn it off or change it? Hi mregen (mregen@discussions.microsoft.com), in the newsgroups you posted: || I have my purchase orders set up as a Publisher document. When our || organization upgraded from Publisher 2000 to Publisher 2002, the new || version set parameters on the page numbers. This was one of those...

insert downloads into power point
i downloaded an application called "BioDigital Simulator" of an animated cleft lip/palate surgery and need it put into my power point presentation, but can't fiugre out how to do so.... very frustrating... What kind of file is this application? Is it a video? If so, what kind? MPEG? AVI? MOV? Or is it an EXE file? Something else? Which version of PPT are you using? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover...

Insert dataset to another database
I'm posting this to this group also since it pertains to queries, primarily. Hello, Using VB6/ADO, I'm thinking I can create a recordset and insert it into another table in a different Jet database, all within the execution of a single query. But, I can't seem to get it to work, even in an experiment in Access 2K. My sql in Access 2K: SELECT D.lorder as Ord, A.Lorder FROM [;Database = C:\MyDocuments\Acc2K\Wrk.mdb].OrdersData as A INNER JOIN [;Database = C:\Access\Work\Sales06.mdb].Detl1 as D On D.Lorder = a.lorder WHERE ((D.fg)= 'MXX-NC' Or (D.fg)= 'MXX.NC')...

Insert a blank row
Hi, I need some help to insert a blank row in a range where column A has a series of dates. There will be several of the same dates and I need to both sort the dates and then insert the blank row at the end of each sequence. In the blank row I need to total figures that will be in columns B through to G. Thanks, Jim S Hi Jim maybe an easier solution 1. Sort your data (use 'Data - Sort', e.g., column A ascending) 2. Use 'Data - Subtotals' This will insert a row after each date and calculate subtotals automatically for you HTH Frank anon wrote: > Hi, > I need some...

text field validation 12-07-04
I am trying to create an validate event for Text fields on CRMAccount address fields. Whenever you try to change the values of these fields, it should alert the message. Here is the code like: In sfa\accts\acct_cont.js file, under window.OnLoad function: function window.onload() { ..... .... try { crmForm.address1_line1.onblur = validate; } catch (e) { alert("Error: " + e.message); } } function validate() { if (crmForm.ownerid.value == "{669282E2-2725-47A1-8234-3BD107C49487}") { alert("Not a authorized user. Please notify ...

Enable Command Button After Entering Text
I am a beginner with Excel VBA programming and would greatly appreciate any advice on how to accomplish the following. I created a user form with three text boxes (txtName, txtDescription, txtProcess) and two command buttons (cmdAdd, cmdCancel). I do not want the cmdAdd button enabled until a user enters valid text into both the txtName AND txtDescription text boxes. I have the Enabled property for the cmdAdd button set to False. How and where do I code the procedure to set the cmdAdd control's Enabled property to True as soon as the user has entered valid text in BOTH the txtNa...

help with conditional formula for a group of values
I need help with a conditional formula. Lets pretend A1 has a value of 15 and in the A2 cell I wish to have a conditional formula that states if the A1 value is greater than > 10 and less than < 20 - if this is true (which it is) the value will be "dog." If A1 has a value of 21 then this falls in the "cat" category i need to add another condition so that if the value in A1 is greater than >20.1 and less than <28.99 if this is true than the value input for A2 should read "cat." All false inputs should read "0" so if the A1 cell has ...

How do I insert a letter into an existing word document?
I am working on a large document that I need to add several letters that are on our letterhead. If I cut and paste the letter, the letterhead header becomes skewed. This is just one issue I'm having with the document! Also need to know how to merge 3 separate documents together once I insert the letters that I need! Help! What do you mean by "skewed"? To preserve the data from the header of the document, you will need to insert it into a Section of its own separated from the rest of the document by Next page Section breaks before and after it with the headers...