conditional formating #4

I have an annual report spreadsheet comprised of  15 rows (1 - 15) and 5 
columns (A - E).  In columns "B" and "D" I add numbers from the current 
year, leaving future year cells blank.  I highlight in yellow the blank 
future cells in columns "B" and "D" by a conditional format "formula is = 
ISBLANK(B9)".  I leave past year cells in "B" and "D" with numbers but no 
highlight colors.  I wish to highlight in green the current year numbers in 
columns "B" and "D", which will always be a cell above the first yellow 
blank cell, and a cell below last years non-highlighted numbered cell.  Each 
year as I add new annual numbers to column "B" and "D", the previous green 
cells would change from green to no highlight and the previous yellow blank 
cell would change to a green highlight.

What would condition #2 be in the conditional format formula?

Thank you for your time and help.

Regards,
Gary 


0
allge (63)
3/16/2006 5:13:27 PM
excel 39879 articles. 2 followers. Follow

2 Replies
317 Views

Similar Articles

[PageSpeed] 40

Assuming that your dates for each year are listed in column A you coul
use these two.  You'll need to modify your other condition.

For 1st condition = Green

=AND(YEAR(A9)=YEAR(NOW()),B9<>"")

For 2nd condition = Yellow

=AND(ISBLANK(B9),YEAR(A9)>YEAR(NOW()))

This will color cell B9 green 

Format as needed.

Does that Help?

Stev

--
Steve
-----------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=757
View this thread: http://www.excelforum.com/showthread.php?threadid=52316

0
3/16/2006 6:13:00 PM
Steve,

This is a test response to your answer.  I've been trying to respond with a 
picture of my spreadsheet, but it does not seem to work.  I'm wondering if 
attachments are not allowed, thus my previous attempt was blocked.  I am not 
real fairmiliar with news groups so I'm most likely selecting an incorrect 
option.

Thank you for your help and patience.

Regards,
Gary


"SteveG" <SteveG.24ryey_1142532900.7683@excelforum-nospam.com> wrote in 
message news:SteveG.24ryey_1142532900.7683@excelforum-nospam.com...
>
> Assuming that your dates for each year are listed in column A you could
> use these two.  You'll need to modify your other condition.
>
> For 1st condition = Green
>
> =AND(YEAR(A9)=YEAR(NOW()),B9<>"")
>
> For 2nd condition = Yellow
>
> =AND(ISBLANK(B9),YEAR(A9)>YEAR(NOW()))
>
> This will color cell B9 green
>
> Format as needed.
>
> Does that Help?
>
> Steve
>
>
> -- 
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=7571
> View this thread: http://www.excelforum.com/showthread.php?threadid=523168
> 


0
allge (63)
3/16/2006 10:51:27 PM
Reply:

Similar Artilces:

Formatting tabs in a workbook
Is it possible to format a workbook so that a tab is set for each day of the month? I do daily reports on excel and I would like the fromat to goautomatically throughout the workbook. Hello Glen If you mean automatically create new workbook and name worbook tabs with numbers from 1 to number of days in month, this could be done with some programming. HTH Cordially Pascal "Glen" <Glen@discussions.microsoft.com> a �crit dans le message de news:B78FF460-2A77-4540-92E3-78C5137A0AA1@microsoft.com... > Is it possible to format a workbook so that a tab is set for each day of...

NDR 4.4.6?
I get the following error when attempting to send to a particular DL. A configuration error in the e-mail system caused the message to bounce between two servers or to be forwarded between two recipients. Contact your administrator. <SERVER #4.4.6> How do I check the possibility of looping? Do the users in the DL have forwarding turned on within their mail properties in AD? "pscottlee" <pscottlee@discussions.microsoft.com> wrote in message news:196F18E2-895E-4AB9-A40E-8D2A6F2E0378@microsoft.com... >I get the following error when attempting to send to a particu...

Conditional formating using named cell
Hi. In a pivottable in Excel 07 I have the monthly results for a department. I use conditional formating to indicate if the spend value for year to date exceeds the total-year-plan with more than a certain pct. This pct will change over the year, depending on the month shown. Till now I have typed the value in the conditional formating: =3D($J9*4/12)<$G9 [J is total-year-plan and G is the spend value for year to date.] I would like to make the formula dynamic, and have it to refer to a cell called ValueForRed. I have tried to tye =3D($J9*ValueForRed)<$G9 but it does not work. Any s...

Upgrade .pst file from 2002 to 2003 format
I want to take advantage of some of the new features of the Outlook 2003 ..pst file format, namely the bigger capacity. After I upgraded OfficeXP to Office 2003, the settings migration worked just fine. Now I want to upgrade the .pst file format. Is that necessary and if it is, how do I do it? Thanks, ~alan Outlook 2003 will create a PST file in the new format by default. Just copy the contents of your old PST file into a new one created by Outlook 2003. Be sure you then set the new one to be your default. -- Russ Valentine [MVP-Outlook] "~~Alan~~" <alan.shepro_NOSPAM...

Change default number format
Anyone have any idea how to do this? You could create a template workbook (*.xlt) and format all the cells with the numberformat you want. Then use that whenever you want to use that special format. If you want to use that number format for all new workbooks, name the template workbook book.xlt and store it in your XLStart folder. If you save another copy as sheet.xlt, then any new sheet added to an existing workbook will inherit that number format. mbrucker wrote: > > Anyone have any idea how to do this? -- Dave Peterson ec35720@msn.com Right click the cell. > format cells ...

2007 print merge 4 postcards/4 different addresses per page
I have read the previous posts regarding this issue. I cannot resolve my problem. I have created a 1/4 page postcard. Information on page one and successfully merged addresses on page two. I only have one postcard on my screen and as I scroll through, I see all the correct information in the merge. I am having trouble printing the merge. I want 4 postcards on one 8 1/2x11 piece of cardstock. One side with the same information and the other side with four different addresses from my merge. I am getting side one with information and side two with four duplicated addresses. The second pi...

CLIPBOARD #4
In EXCEL, there is a small window that pops up that shows the cell references and the copy of the clipboard. How can I activate that window??? Larry Hi Larry, View (menu), Toolbars, Clipboard --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "FLKulchar" <flk2575@comcast.net> wrote in message news:60c301c3e5f1$2f5403a0$a001280a@phx.gbl... > In EXCEL, there is a small window that pops up that shows > the cell...

CRM 4.0 for the Visually Impaired
I heard that CRM 4.0 has some functionality for the visually impaired. Can anyone point me in the right direction to find out more about this? Thanks. Hi Carl; I think there are a few improvements: a) Required fields now have an asterisk beside the label to help distinguish them for people trouble distinguishing between colours. b) There is a high-contrast setting for each user in Tools-->Options. From what I understand, this provides a better experience for users when Windows is running in high-contrast mode. There may be others, but those are the ones I've seen Dave Irelan...

How to convert fraction format to text format ......
....... and still shows the fraction instead of decimals? I have formatted a range of cells to fraction. Then I key in the = following values:- 1/2 1/5 1/4 1/3 2/7 9/16 etc. etc. Then I format the cells to text and the fractions are converted to = decimals. How can I have both text format and the fractions displayed = as above without writing a formula? I tried custom format, paste special>values etc. and had no luck. I am also having trouble with a formula which works most of the time but = not *all the time*. e.g. A7 formatted to fraction>8/16 then I key in 11/16.=20 The form...

Windows Media Player Format GUIDs
To display MP4 videos from our WPD device, we report to Windows Media Player that the WPD_OBJECT_FORMAT of the object is WPD_OBJECT_FORMAT_MP4. Where will I find the GUID for MOV videos that Windows Media Player now supports? Thanks, Mridul. ...

Conditional Sum - Moving Average
Dear Expert, Want to calculate Moving Average in this format. But Sum is conditional Say B2 to G2 are dates like this. Say B3 to G3 are the prices. A3 is Stock name A2 is empty Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. If I put a 7 in D1 ... then it can calculate the 7-day moving average in E1 Moving average is dependent on the cell address to work out. I have asked similar question before ... but the date is in vertical format. This time .... If I present in horizontal format ... hope expert can still ...

date format
How do I change the date format to 10-Nov-2003 instead of 11/10/2003 in Outlook? change the date format in windows control panel, regional settings. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, Visual Quick Start Guide - OneNote 2003 Outlook Tips: http://www.outlook-tips.net/ http://www.poremsky.com - http://www.cdolive.com Expert Zone http://www.microsoft.com/windowsxp/expertzone Search for answers: http://groups.google.com Most recent posts to the Outlook newsgroups: http://groups.google.com/groups?as_ugroup=microsoft.public.outlook.*&...

Find & Replace formating
Any suggestions? Problem with Find & Replace and formating. Sally tried to do a find and replace with the following: find "US", replace with "United States." Instead of replacing it as she has indicated, it replaced it with UNITED STATES. When she undid this and tried to do it again, she looked at the more specific find/replace options (where you can search for a certain format)--in that section, all of the format boxes under "effects" were checked. (I'm not ever sure how that is possible. Superscript AND subscript?) She unchecked them, ran the find rep...

Microsoft Outlook 2003 client on a SBS 4.5,
When setting a new appointment in a shared calendar using a Microsoft Outlook 2003 client on a SBS 4.5, only the user who sets the appointment can view the appointment. The user who owns the calendar can only view hours after it was set. Sometimes that user can't view the appointment at all. In addition, the user which sets the appointment has problems viewing reoccurring events. Some of the reoccurring events are viewed by that user and some aren't. In news:D0A98849-59FC-46D8-904F-E370B385ACC9@microsoft.com, ?? <@discussions.microsoft.com> typed: > When setting a ...

Conditional Chart/Graph?
I'll try to simplify things as much as possible. I currently have a numerically ordered list. I need to only display the list up to a certain number. The certain number which I need to list up to changes depending o other variables. I also need to display this restricted list in a graph/chart. I could conditionally format the list to only display numbers in readable font colour up to the certain number, however when i then pu the data into a chart, the values which are unreadable on the workshee (e.g. because they are the same colour as the background) are displaye in the graph. Any...

Installing CRM 4.0 not seeing AD
When I attempt to install CRM 4.0, I get the the part where you tell it where the OU is in your active directory. I use the browse button, and it brings up the search window with my domain listed. I open my Domain, and then select the folder for Domain Controllers. No servers are listed. I am using Server 2008 standard for both CRM and my Domain controller. ------=_NextPart_0001_56B70D08 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Joel, Thanks for posting in. This is Ken in Online Partner Support. Please select the OU for CRM install, not server. When you select an O...

bring back ability to format subtotals in pivot tables
You used to be able to highlight all of the subtotals for a given field and with one click and then either shade, change font, etc. and that seems to no longer be possible. If someone knows how to do this, please let me konw! If you enable selection in the pivot table, you'll be able to select parts of the pivot table, and format them. To enable selection: From the Pivot toolbar, choose PivotTable>Select Click on Enable Selection "Pam-ADPT" wrote: > You used to be able to highlight all of the subtotals for a given field and > with one click and then eith...

format cell with maximum value
I have a list of number, say 10rows by 10 columns. I want to specify a conditional setting for each cell so that if the cell has the highest value in its row, the cell will be coloured red. So I will get 10 cells that will be coloured red. How do I do that? Thanks Use conditional formatting. If your data starts in A7, then in A7, Format > Conditional Formatting. Use the drop-downs and text box to create your condition: If cell value is equal to =MAX($A7:$J7), then set your format appropriately. Copy the format through your entire table. "Frank Drost" wrote: > I have ...

Conditional Statement in Excel
Hi, I'm trying to create an if statement that whenever the user selects "Debit Memo" from the dropdown menu in column A (has other items-- Credit Memo Check, Transfer), the user would be forced to enter the corresponding value in column F (Debit). If the user attempts to enter the value in Column G (Credit)column, an error message would be generated "Incorrect Account". Grateful for any assistance in resolving this matter. WC One way: Select your cells in Column G (say, G2:G100, with G2 the active cell). Choose Data/Validation, and enter Allow: Custom Fo...

Formatting incomming mail
Hi, Is it possible to format incomming mail, so that lines starting with "> " show up in a different colour. I want to mimic what ximian does with incomming mail as it makes it very easy to search for "inline" comments in emails. Thanks, Akshay No, if you have a message marked with > it means the message is in Plain Text which doesn't support colors. -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Setting Permissions on a Mailbox -Create an Office ...

Any way to round to denominations of 5000 using number format code
I'ld like to have numbers be shown as rounded to the nearest 5000. Thus, if a cell has the value 92,345, I would like it to show 92,000. I want to do this with the format codes...and NOT with a worksheet fxn if at all possible. Also, is there any way to modify this so that it would always round up or down? Thanks. On Mon, 18 Oct 2004 13:09:04 -0700, "consumer" <consumer@discussions.microsoft.com> wrote: >I'ld like to have numbers be shown as rounded to the nearest 5000. > >Thus, if a cell has the value 92,345, I would like it to show 92,000. I >wa...

Adding conditions for Sales Process in Workflow Manager
First, here's the low-down: I am a CRM (v3) user and one of the dreaded sales-guys that many posters of this forum fear. CRM has been installed and is generally running 'out-of'the-box', but now we need some customisation it has been put on my lap. I am vaguely technical, but I am very slowly fighting my way through a pretty complicated product. There are 2 features I am trying to enable right now, and I am having trouble: 1. When we view the Sales Pipeline Report we want the Sales Stage field to work. By trial and error, web sites and using this forum I have now got ...

Date format #12
Hi Is there any way to add "st" or "nd"or "th" with the dat automatically. For eg. if I enter a date 31-12-04 , I want it to be displayed as 31s Dec 2004. or if I enter 30-09-04 it should show as 30th Sep 2004. Appreciate your help Tom -- Message posted from http://www.ExcelForum.com "SMILE >" <<SMILE.16dz02@excelforum-nospam.com> wrote... >Is there any way to add "st" or "nd"or "th" with the date >automatically. >For eg. if I enter a date 31-12-04 , I want it to be displayed as >31st Dec 2004. o...

How to do a conditional sum?
Hi, I have rows like this: USER DATE TIME STATUS NG 03/11/09 09:43:47 IN LP 03/11/09 09:57:45 OUT PJ 03/11/09 09:58:14 IN LH 03/11/09 10:10:11 IN NG 03/11/09 10:39:07 OUT PJ 03/11/09 11:17:25 OUT For any given date and time (or range of dates) I would like to know how many users are IN. Any ideas? I put your data in A1:D6 but I pretended it extended to row 30 Starting in F1 I entered these four value 03/11/2009.....9:0003/11/2009.....10:05 Then I used this formula =SUMPRODUCT(--(B1:B30>=F1),--(B1:B30<=F2),--(C1:C30>=G1),--(C1:C30<=G2),--(D1:D30="IN")) to tell me ...

Text to date format
I have a large file imported from another source into Excel (2003) with a column of dates. The date column format is eg Mar. 09 1963 but some dates only have the year or no date. I want to convert dates to format dd/mm/yyyy or where there is no full date, to return just the year yyyy. Where the full date is available I can convert using formulas or Text to Column, but I have trouble where there is just the year. Is there a method without having to convert manually all the dates with only the year? ...