IF function 05-21-10

I have a list of numbers in column A. Some are formated in 'red' color and 
the others are 'black'. How do i format column B so there is an 'X' next to 
the numbers that are 'red' and nothing next to numbers that are 'black.'

Thank you
0
Utf
5/21/2010 3:42:01 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
827 Views

Similar Articles

[PageSpeed] 38

I would take the opposite approach.

I'd put an X in the adjacent column or leave it blank, then use Data|Validation
to color the cell near it.



gudencough wrote:
> 
> I have a list of numbers in column A. Some are formated in 'red' color and
> the others are 'black'. How do i format column B so there is an 'X' next to
> the numbers that are 'red' and nothing next to numbers that are 'black.'
> 
> Thank you

-- 

Dave Peterson
0
Dave
5/21/2010 4:03:51 PM
Two steps 

Step one create a custom function - if you haven't created a macro before - 
do a search of "custom functions excel" - high level "alt f11" add a module 
and insert the following code.

Function GetColor(myCell As Range)
    GetColor = myCell.Font.ColorIndex
End Function

Step two add the following 
=IF(GetColor(A3)=3,"x","")


-- 
Wag more, bark less


"gudencough" wrote:

> I have a list of numbers in column A. Some are formated in 'red' color and 
> the others are 'black'. How do i format column B so there is an 'X' next to 
> the numbers that are 'red' and nothing next to numbers that are 'black.'
> 
> Thank you
0
Utf
5/21/2010 4:11:01 PM
Perfect! Thank you. 
Do you know of anyway to get column B to automatically update if you format 
a number in column A 'red'. Righ now you have to double click on the column B 
cell and press enter to update it.

"Brad" wrote:

> Two steps 
> 
> Step one create a custom function - if you haven't created a macro before - 
> do a search of "custom functions excel" - high level "alt f11" add a module 
> and insert the following code.
> 
> Function GetColor(myCell As Range)
>     GetColor = myCell.Font.ColorIndex
> End Function
> 
> Step two add the following 
> =IF(GetColor(A3)=3,"x","")
> 
> 
> -- 
> Wag more, bark less
> 
> 
> "gudencough" wrote:
> 
> > I have a list of numbers in column A. Some are formated in 'red' color and 
> > the others are 'black'. How do i format column B so there is an 'X' next to 
> > the numbers that are 'red' and nothing next to numbers that are 'black.'
> > 
> > Thank you
0
Utf
5/21/2010 4:21:04 PM
Sounds like your calculation is set to manual

The way to change it to automatic is different between xl03 and xl07
in XL07

Click the office button (upper left corner)
Excel options
Formulas
click the top left radio button (should have the word automatic immediately 
to its right.
-- 
Wag more, bark less


"Dave Peterson" wrote:

> I would take the opposite approach.
> 
> I'd put an X in the adjacent column or leave it blank, then use Data|Validation
> to color the cell near it.
> 
> 
> 
> gudencough wrote:
> > 
> > I have a list of numbers in column A. Some are formated in 'red' color and
> > the others are 'black'. How do i format column B so there is an 'X' next to
> > the numbers that are 'red' and nothing next to numbers that are 'black.'
> > 
> > Thank you
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
5/21/2010 5:52:01 PM
Automatic Calculation is already on. Yet column B does not update when a 
number in column A is formated to 'red'. Or vice verca, when i take off the 
'red' formating in column A, column B does not update by removing the 'X'

"Brad" wrote:

> Sounds like your calculation is set to manual
> 
> The way to change it to automatic is different between xl03 and xl07
> in XL07
> 
> Click the office button (upper left corner)
> Excel options
> Formulas
> click the top left radio button (should have the word automatic immediately 
> to its right.
> -- 
> Wag more, bark less
> 
> 
> "Dave Peterson" wrote:
> 
> > I would take the opposite approach.
> > 
> > I'd put an X in the adjacent column or leave it blank, then use Data|Validation
> > to color the cell near it.
> > 
> > 
> > 
> > gudencough wrote:
> > > 
> > > I have a list of numbers in column A. Some are formated in 'red' color and
> > > the others are 'black'. How do i format column B so there is an 'X' next to
> > > the numbers that are 'red' and nothing next to numbers that are 'black.'
> > > 
> > > Thank you
> > 
> > -- 
> > 
> > Dave Peterson
> > .
> > 
0
Utf
5/21/2010 6:19:01 PM
There might be a better way however the following code should be inserted

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Calculatefull
End Sub

However, rather than being in a module it has to be in the Micorsoft Excel 
Object (section) make sure you put it in the sheet that you have the 
calculations

Also not deleting the cell information doesn't change the color of the font

You might what to alter the formula to be
=if(and(len(a3)<>0,getcolor(a3)=3),"x","")

-- 
Wag more, bark less


"gudencough" wrote:

> Automatic Calculation is already on. Yet column B does not update when a 
> number in column A is formated to 'red'. Or vice verca, when i take off the 
> 'red' formating in column A, column B does not update by removing the 'X'
> 
> "Brad" wrote:
> 
> > Sounds like your calculation is set to manual
> > 
> > The way to change it to automatic is different between xl03 and xl07
> > in XL07
> > 
> > Click the office button (upper left corner)
> > Excel options
> > Formulas
> > click the top left radio button (should have the word automatic immediately 
> > to its right.
> > -- 
> > Wag more, bark less
> > 
> > 
> > "Dave Peterson" wrote:
> > 
> > > I would take the opposite approach.
> > > 
> > > I'd put an X in the adjacent column or leave it blank, then use Data|Validation
> > > to color the cell near it.
> > > 
> > > 
> > > 
> > > gudencough wrote:
> > > > 
> > > > I have a list of numbers in column A. Some are formated in 'red' color and
> > > > the others are 'black'. How do i format column B so there is an 'X' next to
> > > > the numbers that are 'red' and nothing next to numbers that are 'black.'
> > > > 
> > > > Thank you
> > > 
> > > -- 
> > > 
> > > Dave Peterson
> > > .
> > > 
0
Utf
5/21/2010 6:43:01 PM
That's the problem with UDF's that rely on formatting.  They don't update when
the formatting changes.

You could use:
> > Function GetColor(myCell As Range)
        application.volatile  '<-- added
> >     GetColor = myCell.Font.ColorIndex
> > End Function

But this only insures that the function will recalculate when excel
recalculates.  Don't trust the results until you force that recalc.

That's why I'd use the X and base the conditional formatting on that other cell.




gudencough wrote:
> 
> Perfect! Thank you.
> Do you know of anyway to get column B to automatically update if you format
> a number in column A 'red'. Righ now you have to double click on the column B
> cell and press enter to update it.
> 
> "Brad" wrote:
> 
> > Two steps
> >
> > Step one create a custom function - if you haven't created a macro before -
> > do a search of "custom functions excel" - high level "alt f11" add a module
> > and insert the following code.
> >
> > Function GetColor(myCell As Range)
> >     GetColor = myCell.Font.ColorIndex
> > End Function
> >
> > Step two add the following
> > =IF(GetColor(A3)=3,"x","")
> >
> >
> > --
> > Wag more, bark less
> >
> >
> > "gudencough" wrote:
> >
> > > I have a list of numbers in column A. Some are formated in 'red' color and
> > > the others are 'black'. How do i format column B so there is an 'X' next to
> > > the numbers that are 'red' and nothing next to numbers that are 'black.'
> > >
> > > Thank you

-- 

Dave Peterson
0
Dave
5/21/2010 6:51:31 PM
Reply:

Similar Artilces:

Going offline in CRM 10-05-04
Hey: I recently found out that you may create a CRM Appoinment and have it synch up to CRM by clicking the CRM "Go Offline" button in the SFO shortcut toolbar... Problem is, I keep getting the error "CRM SFO cannot go offline. The Microsoft Server was not found." Has anyone else had this problem? --Dodd > I recently found out that you may create a CRM Appoinment > and have it synch up to CRM by clicking the CRM "Go > Offline" button in the SFO shortcut toolbar... This is true, you can schedule for yourself and it will sync to CRM. However, you c...

Entourage 2008
I just upgraded to Entourage 2008 and am missing my "resend" button for emails - is there any way to get a "resend" button to appear in the toolbar with the Forward, Reply, etc. like it used to in Entourage 2004? Mike On 2010-01-13 07:01:58 -0500, Michael Levin said: > I just upgraded to Entourage 2008 and am missing my "resend" button for > emails - is there any way to get a "resend" button to appear in the toolbar > with the Forward, Reply, etc. like it used to in Entourage 2004? There is unfortunately no button, and by defaul...

GP 10.0 VPC Image #2
Has anyone else had problems extracting the 10.0 image. We received a CD at the Partner's Conference. It fails in the extraction process. Is there another image that is available for download? Joanne Mahoney SDN Jacksonville I don't think there's a way to download it. I had problems with the first disk and got somebody to send me a second disk, which worked. You may want to talk to your Microsoft rep to see if you can get one. The technical specialists should have them. -- Charles Allen, MVP "Joanne" wrote: > Has anyone else had problems extracting the 10.0...

Advanced Find 05-22-06
Hi, I would like to search for services details using Advanced Find and make/create a report with the resources information. For example... I would like to know the users that are working on a specified service? or What resources groups the user belongs and then make a report with that information. If i go throw the resource group area i can create a report with that information, but it doesnt include the user email for example. Any sugestion how this could be done? thx ...

Mode function
Hi, I'm using the following expression to return the mode of a list of numbers: =IF(H1028="","",MODE(H1028:H1031)) If the four checked cells all have values but there's no two values the same (i.e. no mode) what I need is an expression that will return me a default value of 2 rather than the #N/A error value. Thanks for looking, Steve. =IF(ISERROR(MODE(A1028:A31)),2,MODE(A1028:A31)) Regards, Stefi „Struggling in Sheffield” ezt írta: > Hi, > I'm using the following expression to return the mode of a list of numbers: > > =IF(H1028="...

Getting Excel to return Hiragana using code/char functions
I have a Japanese computer which does this perfectly, but when I tried to use the worksheet on an English computer with Japanese language enabled, it only came up with errors. The code it was returning for the Japanese characters was much to low & putting in higher codes meant it didn`t recognise it. What can I do to make it work? ...

Append Query Not Working 05-03-10
My Append query is not working and i am not sure why... Here is my SQL INSERT INTO tblContractPOTracking ( TrackingID ) SELECT tblDocTracking.TrackingID FROM tblDocTracking WHERE (((tblDocTracking.DocumentNumber)="5" Or (tblDocTracking.DocumentNumber)="6" Or (tblDocTracking.DocumentNumber)="7")); Please help! "not working" is a bit vague ... If you want more specific suggestions, please provide more specific description... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and s...

How can i print sequential page numbers 1 to 10, if i have one wo.
I have an excel worksheet that is one page long. I want to print it 100 times with page numbers labeling it 1 to 100. How do i do this? DJN Code from Ron de Bruin's site. http://www.rondebruin.nl/print�.htm#number Sub PrintCopies_ActiveSheet() Dim CopiesCount As Long Dim CopieNumber As Long CopiesCount = Application.InputBox("How many Copies do you want", Type:=1) For CopieNumber = 1 To CopiesCount With ActiveSheet 'number in cell A1 '.Range("a1").Value = CopieNumber & " of " & Copi...

Help with countif function..
worksheet with 3 sheets, want information from sheet 3 to show in a countif cell on sheet 1. New to this and can not make it work somehow. Trying to get a count on information containing the word "clerk". Column with information on sheet 3 contains text with various different combination containing the word clerk. Does this make a difference? Should I not be able to put various different clerks in the function to change the count on sheet 1? Any suggestions I keep getting a 0 for an answer when the cell range on sheet 3 contains over 9 incidences of the word clerk. Try this: ...

NEED ALL FUNCTIONS BY SIZE (LIQUOR-WINE BUSINESS)
i am in the liquor business and everything we do is by SIZE. it would be great to have a data base field for Size. track sales by size discount formula by size( if x size and x quantity then % discount) etc. or is there a way to do this i am not aware of? ---------------- 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 link to open the suggestion in the Microsoft Web-based Newsreader and then click "...

Query Needed 01-04-10
A sample of my data table looks as so: Underlying ID StrikePrice AAPL APVA10C155.00 155 AAPL APVM10P155.00 155 AAPL APVA10C160.00 160 AAPL APVM10P160.00 160 AAPL APVA10C165.00 165 AAPL APVM10P165.00 165 AAPL APVD10C160.00 160 AAPL APVP10P160.00 160 AAPL APVD10C165.00 165 AAPL APVP10P165.00 165 AAPL APVD10C155.00 155 AAPL APVP10P155.00 155 AAPL APVB10C155.00 155 AAPL APVN10P155.00 155 AAPL APVB10C160.00 160 AAPL APVN10P160.00 160 AAPL APVB10C165.00 165 AAPL APVN10P165.00 165 GS GPYA10C155.00 155 GS GPYM10P155.00 155 GS GPYA10C160.00 160 GS GPYM10P160.00 160 ...

Server re-boots every day at 10:00am
My Exchange Server is re-booting at 10:00am almost every day. I have checked everything scheduler removed IBM director. Does any one have an ideas how to trouble shoot this. Thanks for your help! What do the event logs indicate is happening at that time? Have you considered opening a case with Microsoft PSS? -- Ben Winzenz Exchange MVP MessageOne "Maida" <Maida@discussions.microsoft.com> wrote in message news:DAC7F528-612E-4E3C-8AEC-0C939CF90E8B@microsoft.com... > My Exchange Server is re-booting at 10:00am almost every day. I have > checked > everything sch...

Index Function Problem
I need a function that will return all information for "truck 1" on another worksheet. The information below is an example of information on my worksheet. I need a function that will search the data and return all instances of "truck 1" on a separate worksheet. B C D E F Grade Mill Driver Weight Rate 1 ZONE 2 BATSON WILSON 25.00 2 ZONE 3 CAMDEN TROY 26.00 3 ZONE 4 CLW TRUCK 1 27.00 ...

Outlook Client functions
I installed the CRM Server and it appears to be working. I just installed the first PC with the CRM Sales for Outlook. I can't tell what was installed. Other than it showing up in my add and remove programs, I have no evidence that it's been installed. Can someone tell me what I should be seeing if it installed and is working correctly? I found a KB article that told me to remove and re-add the COM Add-in. But that didn't seem to change anything. Thanks, Gary If you received the 'completion' message after the SFOC was installed (and you can see it in add/rem...

Auto play 06-15-10
How can I stop the PC from starting to auto play a movie disk in when I insert it? I was getting a menu with different choices. Thank you "Mike Givens" <micell@ameritech.net> wrote in message news:6677560C-DD38-49D6-BCE3-78AF2D210BA2@microsoft.com... > How can I stop the PC from starting to auto play a movie disk in when > I insert it? I was getting a menu with different choices. > Thank you Auto Play set up is a control panel item under 'Hardware and Sound'. I think 'ask me' produces the menu. Tom ...

GP 10 SP2 SDK Location
Hello! I am trying to find the SDK updates for GP 10.0 SP2 and I can't seem to. Can some one point me to it? I am specifically looking for table changes... Thanks so much! Sharon ...

Crystal Reports 05-12-04
I'm having an issue where every few weeks the default reports need to be republished. I've got the Crystal Reports toolkit and am able to get the reports working again by republishing them. It's just aggrivating! Has anyone else seen this issue? >-----Original Message----- >I'm having an issue where every few weeks the default >reports need to be republished. I've got the Crystal >Reports toolkit and am able to get the reports working >again by republishing them. It's just aggrivating! Has >anyone else seen this issue? >. > It seems th...

CRM 4.0 Connector for GP 10.0
I have found CRM 3.0 connector for GP 9.0. But, i didnt come accross any CRM 4.0 Connector for GP 10.0. Can anyone suggest me what to do now for integerating these two dynamics' products. Hi Jawad, The GP 10 and CRM 40 connector is not availble yet. If you are looking to integrate the two products together, you may try to use a 3rd party product like Scribe. Thye do have a solution for integrating the two products. Darren Liu, Microsoft CRM MVP Crowe CRM http://www.crowecrm.com On Dec 30 2008, 8:48=A0am, Jawad Shafi <Jawad Sh...@discussions.microsoft.com> wrote: > I have fou...

Index/Lookup Function?
Hi All- I need help returning a list of names on a separate sheet. I'll enter "manager 1" in an input cell, and i want to return a list of corresponding sales reps until i run out. I am not sure which formula is best: Index, Lookup etc. A B manager 1 sales rep 1 manager 1 sales rep 2 manager 1 sales rep 3 manager 2 sales rep 4 manager 2 sales rep 5 thanks! hi, you may refer to my article at the following website - http://office.microsoft.com/en-us/excel/HA012260381033.aspx -- Regards, Ashish Mathur M...

workflow rules 10-29-07
Hi, I am trying to make an advanced find view for "incident",namely: if the incident is open and if two days past after the creation of the incident. Can anyone suggest me about this? The advanced find does not support this type of filtering. You have to options to do this kind of functionality within the limitations. You can create a list of all open incidents and sort them by name, this will include the last 2 days but these will be at the bottom of the list. The other option is to create a sql reporting services report, this can support this type of filtering, but requires...

Function to find hyperlink URL
Hi, Is there function to return URL of hyperlink? I don't want to use macros. I'm using Excel 2000 Thanks in advance Nikola Milic >>Is there function to return URL of hyperlink? A worksheet function? Afraid not. -- Jim Rech Excel MVP But you could write your own (a userdefined function). I used ctrl-K (insert|Hyperlink) to create the link: Option Explicit Function GetURL(Rng As Range) As String Application.Volatile Set Rng = Rng(1) If Rng.Hyperlinks.Count = 0 Then GetURL = "" Else GetURL = Rng.Hyperlinks(1).Address ...

VB
Can anyone tell me why Cells.Interior.ColorIndex = Int(Rnd * 56) + 1 works ok using the forms toolbar with a button click but does not work if you use the control box toolbar. Maria, What does "not work" actually mean? What version of Excel are you using? Open the Properties window for the control button and change the "TakeFocusOnClick" property to False. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "maria" <maria.stothard@sylvania-lighting.com> wrote in message news:07e...

Dynamics GP 10
We are getting ready to upgrade our Production server to GP 10 in a couple weeks. Some people that were at Convergence this year heard that there is a way(SQL Script) to transfer the new security that we have set up in version 10 from our Dev server to our Prod server. This would obviously save the need to re-create all the new security roles and access. I have read post that mention some tools you can purchase from vendors but we were curious about an SQL script. Has anyone used such a script to perform this task? Thanks very much Scott T On 7 May, 14:49, Scott Trenholm <bcsa...@ho...

functional currency
I set up a new company, and copied some data over. When I try opeining an item its asks me to set the functional currency. I went into setup and tried to set it, it saves doesnt give me any messages but it is still asking me to set the functional currency. This is a new company that was just set up, any direction on this would be great. Thanks! If you go back to your window after you saved can you see the change you made, or it's back the original value? "Dave" <Dave@discussions.microsoft.com> wrote in message news:19DF177D-168D-4C7A-B1EF-38CB7244C939@microsoft.com....

Contacts and Accounts 11-21-03
Hi When I create an order and link it to a contact I would expect it to be linked to the account as well. This seems to not be the case. I created an order (using the Adventure-works sample database) for Ingrid Burkhardt. When I looked under the Sales Order tab for Bike Universe the order was not there. However, when I went to the contacts tab, and opened INgrid, and then went to the Order tab it was there. How is this supposed to work? Thanks Gill I believe this is as designed Gill. The cases work the same way, a case is either linked to the account or a contact - but there is...