difficulty with conditional formatting

I have a spreadsheet that gets updated monthly and looks like the example below

Jan           Feb        march      April        May         June
21,000      23,500     22,224     22111   20,111      25,000

I've asked this question before and have gotten great tips but each month 
when refesh the pivot table it looses all the conditional formatting and I 
start over again.  How do I get the pivot table to keep it's formatting when 
refresehd?


The conditional formatting needed is if #'s go down for three consecutive 
months in a row they are highlighted in red.  I need the first month of 
dropped revenue example above would be March in yellow and then the second 
month they've dropped would be orange (March) and then the third + 
consecutive months (May) would be highlighted in red.  The other problem with 
information received before is that when I apply the conditional formatting 
as condition 1 and then 2 and then 3, even months that have not been 
consecutively down for three months are highlighted and I have to go back and 
take formatting out one by one.  The formatting is to only take place for 3 
or more consecutive drops in a row.  Any assistance with this and also tell 
me how to prevent the formatting from being cleared every month when I 
refresh the pivot table will be greatly appreciate.  There are now over 4,000 
rows in the database so the time it takes is tremendous.

0
Deb (109)
3/23/2005 6:13:07 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
591 Views

Similar Articles

[PageSpeed] 6

Reply:

Similar Artilces:

Conditional formatting for rows?
I find conditional formatting very useful however I would like to be able to highlight an entire row/range of cells if one cell has a given value, using conditional formatting I am only able to highlight the single cell. Is it possible to have a range/row of cells formatted in response to the value of one cell? Thanks select the row and anchor the test cell in a formula such =$A1=20 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FTM" <FTM@discussions.microsoft.com> wrote in message news:35685BA2-2FF5-4054-B9F0-21FBBC248A2C@mi...

Conditional formatting producing inconsistant results?
Hello, I am attempting to format a cell and the results are inconsistant. The formula looks like this: =IF(MOD(C3,0.0005)>0,TRUE(),FALSE()) The results I get are inconstant. If the value of C3 is .2810 it treats it as a true statement, which is not the case. However, most other values are correctly analyzed. I have discovered the reason which is an error that shows up in about the 17th decimal place. I corrected the problem by changeing the formula to look like this: =IF(ROUND(MOD(C3,0.0005),4)>0,TRUE(),FALSE()) Why the error, and is there a way to avoid th...

PivotTable
Hi, I have a PivotTable with two columns - student names and tuition. I highlighted the **grand total tuition** with fill color. Please note that only ONE cell had this format. However, when I changed the column of student names to a row of student names (by moving the "button"), all the tuition (and not just the grand total) had the fill color. Is this a bug? Is there a way to get around this? I know I can remove the fill color first, change from column to row then put back the fill color. Just wondering if there is a better way. Appreciate advice. Epinn ...

Flip Difficulty
I am running Visio 2003 SP1. I have drawn a home floor plan. I select the entire floorplan, and try to "flip" it (for example, I want the garage on the right instead of the left, so I mirror, or flip, the entire plan). I get an error message and then the drawing is mis-configured with lines going in odd directions, like it flipped some objects but not others. Here are two of the error messages 01. "An error (318) occurred during the action Flip Horizontal. Referenced cell Wall.629!Connections.X4 does not exist". 02. "An error (318) occurred during the action F...

Changing Time format in Date column
Hi, When I review email messages in Windows Live Mail I would like to see the time displayed in 24 hour format by each message. Ie, instead of Date: 05/03/2010 4:35 pm I would like it be shown as 05/03/2010 16:35. Any help from anyone would be greatfully appreciated. Thanks, Jon. "Jon" <Jon@discussions.microsoft.com> wrote in message news:54D681D7-2367-434B-AC7A-29AD80A4A828@microsoft.com... > When I review email messages in Windows Live Mail I would like to see the time displayed in 24 hour format by each message. Ie, instead of Date: 05/03/2010 4:35 pm...

Edit cell format
I am using Excel 2003. When I would paste text into a cell from another source, i. e. the internet, word, outlook, I would get a drop down box at the bottom right corner of that cell. When I clicked on the dropdown I normally had 3 choices one was to keep original formatting, one other was to match formatting. I no longer get that dropdown. What can I do to get it back when I paste into my worksheets? Thanks ...

conditional formatting & time stamp
Hello everybody this is my firs post and I do appreciate you help. I am trying to set up conditional formatting so that after the 5th of every month the cell�s text becomes red, regardless of month or year. I have bean able to set it up for a specific date mm/dd/yy but this unfortunately does not help in my particular instance. I need to some how specify to excel that neither the month nor the year are relevant only the day of the month. Also is there a way to time stamp the date you made an entry into a cell? For example I receive a check and input the check amount into the appropriate ce...

.PST Difficulty
If you could follow the dialogue and help me out in anyway....I am a littl concerned as this is my only copy... Dialogue: Question I am getting this error when I attempt to copy the .pst file I have burned to a cd rom to my locasl hard disk: Cannot Copy Sept22: Invalid MS DOS Function I am a little concerned since since the machine I have taken this information from and burned to cd is no lonbger available...Any ideas? Response: How are you trying to copy the file? My Response I am just selecting it from the cd rom..and dragging it to a folder...As well I have tried Copy and Past...

For Each conditionals
This is a general flowcharting question. What is the best way to flowchart a situation where a loop of "foreach" conditionals are to be performed, returning only results that fit a certain criteria? ...

PivotTable Data Series Formatting
Hi all, I'm having no luck solving this problem, so if anyone can help me out, I'd appreciate it greatly. I have a PivotTable displaying the product of a group of information. By product, I mean all values in the series multiplied together. This is called YIELD. I then need to find the DPMO, which (for the uninitiated, lucky you) is how many times we mess up every 1 million times. The formula is (1-YIELD) * 1,000,000 I'm trying to find a way to calculate the DPMO dynamically. I can't calculate the DPMO in the data and just use that as a field for the PivotTable a...

Difficulty in setting hotmail acct. as main (personal) folder
Just recently I purchased a new Pocket PC handheld which came wit inbox(ppc) and outlook 2002(pc). I installed them both and they wor great. I can sync my emails to the unit and read them anywhere. However, I noticed that it will only import items from my persona folder (which seems is a non-html email acct). Question: Is it possible to configure outlook 2002 to make hotmail th personal folder? If not, would it be possible to create a macro tha can transfer all emails from the hotmail acct to the personal folder? It would be a time saver. Thanks, Hovi ----------------------------------...

cell formatting very slow
I am running Excel 2000 on a 500mhz AMD K6-3 with 256mb ram and win98se. Doing simple cell formatting, usually takes excessive amounts of time. For instance, on a simple work sheet with 110 rows, and 12 colums, removing all borders from a 12x12 area takes over ten seconds - one would think it would be nearly instantaneously. Bolding a single cell can take 5 seconds. All current fixes are on the ms software. Are there some settings I can change to speed things up? Barry Just some guesses: Try closing excel and cleaning up your windows temp folder--it might help and it can't hurt. And i...

How can I use conditional formating between two sheets in one work
If employee number 5263 found in sheet 2, show the raw in main data on sheet 1 in red, for instance. ok this is how i did it use a helper column and a vlookup formula back at sheet 2 =IF(VLOOKUP($G$11,Sheet2!$A$2:$A$8,1,FALSE)=A8,"found","") so if employees name is found on sheet 2 this formula returns found on the same row as the emplyees name conditional formatting for this row is =c8="found" i had to change the formatting formula for each row,hopefully there is an easy way! -- paul paul.shepherd@nospamparadise.net.nz remove nospam for email addy! "...

Select Text with Similar Formatting is not enabled
In Word 2007, I have several instances of text formatted with small caps. I should be able to click inside one of the formatted words and from the Select menu, Select Text with Similar Formatting. However, that option is greyed out / disabled. Does anyone know why and what the fix is? Thank you! I found the answer: Keep Track of Formatting must be enabled in Word Advanced Options. "Trudy" wrote: > In Word 2007, I have several instances of text formatted with small caps. I > should be able to click inside one of the formatted words and from the Select ...

HELP modifying macro to delete rows if more than 1 condition met &
I got his code and it works fine but wondering if it can be modified to delete not only rows that have "TRUE" in column in A but other 3 or 4 different calues in column B... Sub UPDATE_TENURE() Dim MyRange, MyRange1 As Range Dim LastRow As Long Set Sht = Sheets("Agent Tenure") LastRow = Sht.Cells(Rows.Count, "A").End(xlUp).Row Set MyRange = Sht.Range("A1:A" & LastRow) For Each c In MyRange If UCase(c.Value) = "TRUE" Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else ...

cell conditioning
basically im very new to excell and im trying to create a sales shee for my company (tesco) on store performance and at the moment iv conditioned the cells so that when the LFL targe sales are down the cell goes red eg. =$C$12<$C$75 and when there equal to or up its a green which is fine but its currently showing when i have not put the figure for the future months the cells automatically go green now i relise that there will be months where the result will be zero s i cant create another condition that says if zero then condition th cell white i need it so that when the LFL field...

Copy/Paste Formatting
In Office XP (Excel XP) there is a new feature (a.k.a. dumb pain in the butt option) that causes a formatting icon to pop up every time I copy/paste in Excel. Hwo in the hell do I turn this very annoying 'feature' off? Tools|options|Edit tab Uncheck the "show paste options button" Ima Lostsoul wrote: > > In Office XP (Excel XP) there is a new feature (a.k.a. dumb pain in the > butt option) that causes a formatting icon to pop up every time I > copy/paste in Excel. Hwo in the hell do I turn this very annoying > 'feature' off? -- Dave Peterson ...

Danish or German Number Format
Dear All To format a 1000 number such as 12,897.45 to show 12,9 we use the following custom format cell. #,##0.0,"K"_);[Red](#,##0.0,"K)";0"K"_);@ Does anyone know the equivalent Danish or German format, i.e. when regional settings are set to Danish(Denmark). Thanks in advance. Alan Hi Alan I believe those formats will adjust themselves to te regional settins in question. You can change country/regional settings in the control panel yourself and check it out. HTH. Best wishes Harald "Bill" <someone@microsoft.com> wrote in message ne...

Difficulty importing library with VC++
Hello, I am having a small difficult using the SetupAPI library. I made the call: SetupDiDestroyDeviceInfoList and when I build my code, it tells me that it is unable to resolve the reference to __imp__SetupDiDestroyDeviceInfoList. I went to MSDN and I found that this is located in SetupAPI.lib. In Visual Studio C++ .net, I went into the Project Properties and under linker->Generl->Additional_Library_Directories, I added the path that will point to SetupAPI.lib which has the function defined in it. It still doesn't work. Then I thought that maybe MSDN lied to me, so I disected t...

How do I delete cell formats but not the content...
I am trying to copy data from Excel to use in Word and would love to get rid of the cells but not the data that is stored within the cells. What did you try? And what happened when you tried it? I usually select the range, edit|copy and Edit|paste into MSWord (using Office 2003 menus). On 05/17/2010 10:27, Penndaryl wrote: > I am trying to copy data from Excel to use in Word and would love to get rid > of the cells but not the data that is stored within the cells. ...

Date format in Receipts
Hello: My boss wants to add the Due Date on each receipt that uses Account as tender. I created the variable on XML code using: <SET name="DueDate" type="vbDate"> Transaction.Date + 20 </SET> And print it with: <IF> <CONDITION> (Tender.Descriptor.TenderType = tenderAccount)</CONDITION> <THEN> <ROW> "Due Date|" DueDate </ROW> </THEN> </IF> This works, but the DueDate is printed as a number. How can I change its format to MM/DD/YYYY. I tried using all VB Date function, but no one worked. Please, hel...

Conditional Formatting #46
In Excel 97, is it possible to have more than 4 conditional formats? Maybe up to 6 or 7. I am looking at changing the color of a row (say columns A through H) based on the value in column H and is a date format. An example would be: - If the date in H1 is past today, cells A1 through H1 are Red. - If the date in H1 is between tomarrow and 7 days from today, cells A1 through H1 are Blue. - If the date in H1 is between 8 days from today and 14 days from today, cells A1 through H1 are Yellow. - If the date in H1 is between 15 days from today and 21 days from today, cells A1 through H1 are...

If or conditional formatting?
Hi Folks Can some one please help me on a formula? I've been messing with it but can't quite get there. I have two coloumns with dates or blanks and I need a formula to bring back the status of the data A B Status date Blank 1p payable date date 1p paid Blank Blank 1o outstanding I have tried an if statement but can't get it to bring back the status of the blank cells, can anyone help please? Try the below formula... =CHOOSE(SUMPRODUCT(--(ISNUMBER(A1:B1)))+1,"1o outstanding...

Leopard Upgrade -> File Format Not Valid -> every file is corrupted??? = I am up the creek
Did you try to FORCE it to open? That is, dragging the file ( + option) onto Excel in the dock? ...

format cells as zip code not working
I have selected a column, choose Format, Cells, Special, Zip Code, but some of the cells continue to show up in error checking as "text". The spreadsheet is thousands of rows long, and I do not want to have to go through every single error to change text to number or zip. Why is the formatting not changing all cells? (I am copying & pasting the rows that have name/address/zip from another Excel spreadsheet I have - but I have changed the formatting on the zip code column to zip code in that spreadsheet as well to no avail). Formats don't change the underlyi...