Conditional Formatting - formats cells with no data

I have a row of cells that calculate the totals for their columns but I
force to be blank via IF statements until certain conditions are met.
Once these conditions are met, the total appears.  I'd like to also
highlight these total cells via conditional formatting.  My conditional
formatting input is simply "If Cell is Greater than 0, format".  For
some reason all the cells format as though they are all > 0 yet they
don't display anything.  Are there some internal rules that specifiy
when a cell is really 0?  Is there another way to do this that won't
care what the formula is behind the scenes?  All the cells in question
are foratted as "accounting"

Thanks,
Mike

0
minspu20 (1)
2/24/2006 10:52:46 PM
excel 39879 articles. 2 followers. Follow

2 Replies
538 Views

Similar Articles

[PageSpeed] 15

Rather than "IF cell > 0" in your formula, you could try
"IF(NOT(ISBLANK(cell)))" then choose the conditional format.

Hope this helps.

Pete

0
pashurst (2576)
2/24/2006 11:46:07 PM
There are a number of ways that you could attack this, testing for "",
rounding to 2 or 0 decimal places, but it is probably best if you show the
formula that populates the cell first.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"mikey" <minspu20@centurytel.net> wrote in message
news:1140821566.180915.161100@t39g2000cwt.googlegroups.com...
> I have a row of cells that calculate the totals for their columns but I
> force to be blank via IF statements until certain conditions are met.
> Once these conditions are met, the total appears.  I'd like to also
> highlight these total cells via conditional formatting.  My conditional
> formatting input is simply "If Cell is Greater than 0, format".  For
> some reason all the cells format as though they are all > 0 yet they
> don't display anything.  Are there some internal rules that specifiy
> when a cell is really 0?  Is there another way to do this that won't
> care what the formula is behind the scenes?  All the cells in question
> are foratted as "accounting"
>
> Thanks,
> Mike
>


0
bob.phillips1 (6510)
2/24/2006 11:46:51 PM
Reply:

Similar Artilces:

Publisher catelog merge- cannot open data source file
whether I try to open the source file or open a new one, I get the message: operation cannot be completed becasue of dialog or database engine failures. Please try again later. Troubleshooting advice suggestd to undate the engine from Windows update - can't find it. Does anyone have more specific troubleshooting advice? This article has a solution: You receive a "The operation cannot be completed because of dialog or data base engine failures" error message when you try to perform a mail merge by using an Outlook 2003 contact folder in Publisher 2003 http://support.mic...

Cells formatted for general and text show scientific format
I am working on an Amazon.com shipping confirmation upload. The workbook is downloaded from amazon.com. When I enter the Order Item ID number which is 14 characters, it displays in scientific format. I have tried general, number and text formats. Number format works if there is no leading zero but general and text do not. - Are you formatting the column for Order Item ID number before entering the data? That should solve the problem. - If you are formatting after entering the data, selecting F2 (Edit) in a cell will then put that cell into the correct text format (except for leedi...

Excel Formatted Spreadsheet To SQL ?
Hello, Is there any reasonably straightforward way to convert an Excel spreadsheet, or part of one, to a format that SQL would accept ? Thanks, Bob Usually the method that works is to save the worksheet in CSV format. Then import the CSV format into your database. Access will import CSV and so wil a number of other databases. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=196450 http://www.the...

Multiplying the number in one cell and providing the product in an
I've never set up a formula before, not even the most basic. I need help and I'm not sure if its entirely possible. Please let me know. Labor Hours Labor Cost (95/hr) 2 190 5 950 I need to be able to enter in the number of hours in one cell (under labor hrs) and have those hours multiplied by the cost ($95) and the product displayed in the cooresponding cell (under labor cost). Is this possible? If so, how? EXCEL 2007 1. In cell A2 I have put your 2 (your hours figure). 2. In cell B2 I have put ...

Data Migration manager setup fails.
I'm trying to instaal DMM on an XP machine. Everything goes fine until the installation is stopped. The message in my logfiles is: EVENT-Id:17188 MSCRMPERFCOUNTER. Sorry the rest is in Dutcht: Kan beschrijving voor gebeurtenis-ID (17188) in bron (MSCRMPerfCounters) niet vinden. De lokale computer beschikt wellicht niet over de benodigde registergegevens of DLL-berichtbestanden om berichten van een externe computer te kunnen weergeven. U kunt mogelijk de schakeloptie /AUXSOURCE= gebruiken om deze beschrijving op te halen, zie Help en ondersteuning voor details. De volgende gegevens z...

Cutting a line in a line chart when data series stops
I have a series like so: Jan 10 Feb 10 Mar 11 Apr 12 May Jun Jul And so on - the rest of the year is blank. When I draw a line chart for the full 12 months the series draws a line up to April, then plummets to zero in May. I want the line to just stop at April. If I use a series as above and genuine empty cells for the end of the year this ok, but since my May cell reads =IF($A11<=$D$17,G11,"") the "" does not seem to get recognised as a blank cell. Please help!! Change your formula to =IF($A11<=$D$17,G11,NA()) -- David Biddulph "DannyS" <Da...

series of cells in a column #2
thanhyou so vey much -- ROLG ------------------------------------------------------------------------ ROLG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16066 View this thread: http://www.excelforum.com/showthread.php?threadid=275347 ...

Combine the contents of two cells.
I want to copy the contents of a cell and insert it at the beginning of the text of another cell: ie I want to combine the text contained in two cells into one cell. I can do it manually but would like to use a macro as I have a lot of cutting and inserting to do!! I use Office 2000. Hi! Assume you want to combine cells A1:A100 with cells B1:B100. In cell C1 enter this formula: =A1&B1 If you want a space between values: =A1&" "&B1 Drag copy down to C100 or just double click the fill handle. Now, if you no longer need the data in columns A and B select C1:C10...

Conditional Formatting with Text
Hei gurus, How can I tell CF to turn a cell green if the cells contain text "great" and red if the cells contain "unacceptable??" TX a great deal cell value>="great">format>patterns (choose green) add cell value>="great">format>patterns (choose red) >-----Original Message----- >Hei gurus, > >How can I tell CF to turn a cell green if the cells >contain text "great" and red if the cells >contain "unacceptable??" > >TX a great deal >. > Select the range of cells Format > Co...

Count of cells since first purchase
Hi, Looking for a bit of help with the below. I want to calculate how many weeks have passed since a customer first purchased from me. So for example see below. I want to calculate in cell E2 how many weeks have passed since Account 1 first purchased from me. In cell E3 i want to calculate how many weeks it is since Account 2 first purchased from me. Week 3 April Week 4 April Week 1 May 10 Account 1 2 3 Account 2 1 Account 3 ...

Cells that "appear" empty plot as zero. Do not want cells to plot.
I read thru some of the posts about plotting empty cells and can't seem to find the right answer to my problem. I have a bar chart (Jan thru Dec) which I currently show data running through June. All cells contain the same formula (therefore none are "really" empty). My formula uses the " " to make the cells for July thru Dec "appear" empty, therefore the graph is plotting these cells as zero. I know that I could use 'NA()' and force a #NA error, but I don't want that error message to appear in my cells. Is there a way to not get the ...

Money 2007 -- QIF import
Good Evening! After a little trial and error, I am able to import account transactions from another system (not a bank statement, my actual register transactions) with little trouble except for the date format. (Before someone gets mad at me, I really don't think this has anything to do with my regional settings!) As recommended by an MVP from this forum, I open my CSV file with excel, then use a converter (add-in) called XL2QIF to create a qif output file. The actual "data" in a date field in my original csv file is "060106" (mm/dd/yy) which is June 1, 2006,...

How do I set a cell value based on a formula in another cell?
How do I set a cell value based on a formula in another cell? =IF(D4="P.B.A.",G8,0) ,This works to set the value in the cell the formula is in, say cell A1. I would like to have this formula reside in cell A1 but set the value in cell A2 to (G8,0). Also it would be helpful if I could lock cell A2 based on a False condition from this formula in cell A1. Thanks Gary I note that this entry is also asked and answered in Programming. dingy101 Wrote: > How do I set a cell value based on a formula in another cell? > > > =IF(D4="P.B.A.",G8,0) ,This w...

In-cell progress bars?
Is there a way to have an Excel (2003) cell display a percentage result from a calculation as a shaded percentage of a cell instead of returning a result? It seems like that sort of thing should be in Format Cells... > Shading, but there's nothing there. This is possible to set up in an HTML cell by including a 1-pixel-wide graphic then changing the width attribute of the img tag. Is there anything similar in Excel? Thanks, -j One way you could do this is by means of this formula: =REPT("|",100*A1) if you have a percentage in A1 - it will repeat the pipe character once fo...

Link a picture to data
When I select a group of cells and copy/paste as picture to another worksheet (in the same workbook), I want to have the picture linked to the source data so that the picture changes when the source data changes. I am pretty sure I have done this before, but cannot remember how. Thanks. ...

Locking Cells #6
Is there any way to lock cells in excel so that no body can overwrite it, specially cells containing formulas. TIA Try the instructions here: http://www.officearticles.com/excel/cell_locking_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "newbie" <jcd@refid.com> wrote in message news:O0DJJeihFHA.1248@TK2MSFTNGP12.phx.gbl... > Is there any way to lock cells in excel so that no body can overwrite it, > specially cells containing formulas. > TIA > > ...

Data From A List
WHat I am trying to do is Type in a name of a Client in a cell and have the adjacent cell automatically enter a specific client number.... For example.... if on cell A2 I type in "Smith" I want A3 to read "100". I have a list of names and client numbers..... I have tried LookUp... but to no avail. Any help? Thanks Well, you need to show us your formula, for starters. Tyro "Steve" <Steve@discussions.microsoft.com> wrote in message news:E977F61D-B06C-4E81-9D1F-83094823B1F1@microsoft.com... > WHat I am trying to do is Type in a name of a Client in ...

Filtering data to different worksheets
Hi all, I'm sue this should be really simple to do but I'm struggling to get i to work. I have a list of account numbers and who has keyed each one on. I want to filter down the list so that I have a seperate worksheet fo each person with all of the account numbers (plus other columns of inf ie. address, phone number) that they have done. I've tried vlookup but that, obviously, only looks up the 1st accoun number. Can anyone point out what I'm missing? Thank -- jappe ----------------------------------------------------------------------- japper's Profile: http://www...

How attach a file in one cell
I'd like to know if it'll be possible attach a file (word, pdf...) in one cell. I'm doing an Excel doc. and I need to put some instructions by doc. attached. in some cells. Thanks! In article <A23394A2-B804-4855-875A-7664B90F4F48@microsoft.com>, How attach a file in the cell <How attach a file in the cell@discussions.microsoft.com> wrote: > I'd like to know if it'll be possible attach a file (word, pdf...) in one > cell. I'm doing an Excel doc. and I need to put some instructions by doc. > attached. in some cells. Thanks! Cells can contain...

Why does AutoFilter not show all the data in the pull down?
In an Excel spreadsheet with 12,000 lines, and autofilter on, all the possible selections are not displayed in the filter pull down. I can use custom filter in the pull down, but is more time consuming. I'd rather scroll in the pull down. Is there a limit on the amount of lines shown in the pull down? Or am I missing something? Go to Excel help and enter limits. Excel has dozens of limits. I was surprised not to find a limit on Autofilter which is 1000. Most irritating is only 1024 characters can display in a cell and only 256 characters can be copied to another worksheet. ...

Remove All Bill Data
When I first upgraded to Money Plus, I was having performance issues. Nuking the bills to eliminate the multiple schemas that had built up since Money 1.0 seemed reasonable, so I performed the operation and rebuilt them. However, here I am, 4-5 months later, unable to balance my primary checking account without having Money hang and need to be killed twice during the process. I can't enter transactions that match scheduled bills without having Money hang and need to be killed. So, I nuked the bills again and started over with rebuilding them. Problem solved. Rather than cleaning up th...

data-set too big for Excel
I am using Excel to extract data from a database, but when the extracted data is too extensive, Excel is stuck. Is there any way to expand the data-holding capacity of Excel? I am using Excel 2000. Any help would be appreciated. ...

Help? My formula is displaying in my cell
Hi there - For some reason I have one cell (in a workbook where everything else i working) that when I type in the formula in the formula bar . . . th stupid formula is showing up in the cell. My formula is simply this: =Info!B13 Because I am taking text from a sheet labeled Info in cell B13. I'v done this for about a million cells in this workbook so far an everything else has worked. This one stupid cell is just showing m the formula. Can anyone give me some suggestions to fix that? Much appreciated! Thanks! Jennife -- Message posted from http://www.ExcelForum.com Hi Jennifer,...

How can I data sort to more than 3 levels
Is there a technique or add-in utility that will let me do a data sort in Excel to 4 or more levels at once. Currently you can only do 3 levels, and then you have to separate the data to do more. Thanks for any help -- Pam LaDue ------------------------------------------------------------------------ Pam LaDue's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36773 View this thread: http://www.excelforum.com/showthread.php?threadid=564880 You have replies at your other post. Pam LaDue wrote: > > Is there a technique or add-in utility that will let me d...

Changing data orientation from one sheet to another
Hi, On sheet1 the data is oriented vertically as A1 A2 A3 I'd like on sheet2 the data be oriented horizontally A1 to A1 A2 to B1 A3 to B3 Can it be done? if so, how? TIA Olga Excell 07 Copy the data from sheet1 Select A1 of sheet2 and paste special>transpose. Note: cannot be done if you have more than 16384 rows of data in column A Also, I think you have a typo at A3 to B3.........maybe A3 to C1? Gord Dibben MS Excel MVP On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote: >Hi, >On sheet1 the data is oriented vertically as >A1 >A...