Complicated conditional formatting

Here's a breakdown of what I'm trying to do...Employee scheduling
worksheet that is easy to read and follow.  There are 5 functions
employees can have, each given a specific color via conditional
formatting.  Each column represents a period of time during the work
day, each row a different employee.  As it stands now, for the
duration that the employee will work, I simply enter a 1, 2, 3, 4, or
5 corresponding to their function, and it will color each cell with
that value.

The problem I run into, is in a separate sheet, I'm wanting to use
conditional formatting to apply the same codes to a summary sheet.
This summary sheet includes total hours worked each day of the week.
Is there a way to frame my logical test in conditional formatting to
scan a range of cells in a separate for any value entered and to
assign the summary sheet cell the corresponding function color?  (i.e.
- if employee A worked function 1 at any given time on Day 3, Summary
Sheet cell corresponding to Employee A on Day 3 will be assigned
function 1 color)
0
5/6/2009 8:19:31 PM
excel 39879 articles. 2 followers. Follow

0 Replies
432 Views

Similar Articles

[PageSpeed] 33

Reply:

Similar Artilces:

Outlook XP / html format problem
Hi, Have a user with a wierd email problem in Outlook XP/2002 (exchange server). When sending an email, it seems that only the subject is sent, and the whole message body is blank. I've narrowed it down to sending email in HTML format. He can read any email in HTML format but when he replies or forwards, there is nothing in the message, just a subject. If I set to Rich-text or Plain text and send new message, it is fine. If I get an incoming HTML message, and forward as a Richtext ot plain text, it is fine. I've created another email profile, with no luck. I've r...

Excel amount field formatting error
Excel 2000 version 9.0.6926 sp-3. My amount is 18.00. I select format-number-currency and my amount becomes $1800.00. Always adding 2 zeroes! It formats correctly if I enter just 18. If my amount is 18.25 it formats it as $1825.00. I've tried several different formats with no success. My computer is an hp pavilion xt926. Can someone please help me?!?! Thank you. Lynn Hi, Lynn. Thanks for providing all the info---most people don't. Go to Tools-->Options, and click the Edit tab. Untick "Fixed Decimal" thingee. :) ******************* ~Anne Troy www.OfficeArticles.com &q...

basic format question...I think !!
I have multiple pages on one sheet running left to right. Is there a way to adjust the height of one row on one page with out effecting the row across the whole sheets on either side of it. I know I can merge or insert rows but I would like to just click and drag the selected cells until I get the correct height. Thanks -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- JG, The entire row must have the same height. Avoid merging cells -- it cause...

Conditional Formating (If statements)
In the conditional formatting box how would I make B4 turn yellow if F12 is between 10 and 15? Right-click Sheet, click 'View Code', and paste this into the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) Dim rnArea As Range Dim rnCell As Range With rnCell If Range("F12").Value >= 10 And Range("F12").Value <= 15 Then Range("B4").Interior.ColorIndex = 6 Else: Range("B4").Interior.ColorIndex = 0 End If End With End Sub -- Ryan--- If this in...

2003 Form fields losing formatting in 2007
Hi there, I have some forms, created in Word 2003, which when opened as a .docx, all form fields revert to Times New Roman font but if they are opened as a .doc, they are in the correct font of Arial. Anyone got any ideas why and how I can fix this? Thanks very much. This doesn't make any sense. Documents always open in the format in which they are saved. What *exactly* are you doing? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com ...

EXCEL
Is it possible to put more than 3 conditional formats? I want to put around 8... If 0-5 blue, If 6-10 red, If 11 - 15 brown etc.... Thanks.... No, I believe 3 is the maximum. >-----Original Message----- >Is it possible to put more than 3 conditional formats? >I want to put around 8... If 0-5 blue, If 6-10 red, If >11 - 15 brown etc.... >Thanks.... >. > You'll find that you can't add more than 3 conditions. i.e. the answer to the OP's question is No. You would have to use an event macro (maybe On_Calculate) to scan the cells and set the colors if ...

Icon sets - conditional formatting
How do I get Excel 2003 to do this? I want to be able to add increase and decrease arrow icons to a column of numbers . Thx Upgrade to Excel 2007 or see these products; http://www.ozgrid.com/excel-add-ins/mini-charts-excel.htm http://www.ozgrid.com/excel-add-ins/spark-maker.htm -- Regards Dave Hawley www.ozgrid.com "Lou" <Lou@discussions.microsoft.com> wrote in message news:37AE35EE-B3C4-46B2-98F2-8A0B9627E12D@microsoft.com... > How do I get Excel 2003 to do this? > > I want to be able to add increase and decrease arrow icons to a colum...

Adding sums in rows and columns and colouring cells with conditions
Hi all, I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another...

Opening Page format
Using MS Publisher 2002, I want the opening page to show only a blank page, not a graphic showing - in a third of the screen - a selection of "scheme - options - designs" Help/advice will be appreciated. If it takes up a third of the page you must be using 640 x 480 - UNBELIEVABLE Go to menu Tools > Options tab General second item down - DE-select -- On Wed, 10 Sep 2003 17:44:07 +1000, "���MS�Publisher���" <donotknock@nobodyhome.com> wrote: >If it takes up a third of the page you must be using 640 x 480 - >UNBELIEVABLE I'm not. > >Go to me...

Wait Activity Condition
We have an activity that is added to the lead record automatically at the time the lead is created. It is a phone call activity for our reps to followup with the lead. We also have a sales process (workflow) set up for once the lead is converted to an opportunity. My question is: How can I keep the lead from being converted to an opportunity until the phone call activity on the lead is completed? I have looked at the Wait Activity Condition but can't seem to get anything to work. Any suggestions appreciated. Thanks, Wendi ...

Work around Conditional Formatting
Dear all, There are 2 worksheets (Sheet1, Sheet2) containing similar data in my workbook. I want to underline the text in a cell in Sheet2 if the text in that cell is the same as the cell in the same place in Sheet1. (For example, if Sheet1!A1 = Sheet2!A1, then Sheet2!A1 is underlined.) Can I write a user-defined function to check whether the contents in two cells are equal? Thanks in advance. Best Regards, Andy Chips has lots of stuff on duplicates, see www.cpearson.com -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Andy Chan" <chankhan...

Text emails do not format
All of my incoming emails in Outlook 2003 are coming in as one long line of text. I've gone into my email options and turned off "Remove extra line breaks from text emails" but the problem still occurs. Does anyone know how to prevent my incoming text emails from turning the entire message into one long paragraph? Thanks! Fury Maybe the issue is with the person (or people) sending it. As a test, log into your webmail account, send yourself and email and then go into Outlook 2003 and see what it does... >-----Original Message----- >All of my incoming emails in ...

second() format
Is there a way to enter 3.5 seconds and display decmial point in the second field? Use a custom format like: hh:mm:ss.00 ???? Frankie wrote: > > Is there a way to enter 3.5 seconds and display decmial > point in the second field? -- Dave Peterson ec35720@msn.com ...

Simple Format for Customer Statement
Hello All Is there available another format for printing customer statements? I'm mostly interested in a format that shows charges and payments without all the details and allocation to invoices. In other words I need a statement is the old fashion simple (Debit, Credit, Balance) format. Thank you Thanks Morris, I'll try it out. "Morris Paint" wrote: > This is our current statement. We are having another one written that shows > current period transactions in one area and all open invoices in another. > > "Mario" <Mario@discussions.microsoft...

MS Word Formatting
When I start a document and use font size 36 on the first page and go to the second page and change the font it changes it on the first page. Also when I highlight a few words to bold or underline it bold or underlines everything in the whole document. Does anyone know where I change this setting?? HELP PLEASE! For Word you need to go to the Word Newsgroup. If this is an issue in Publisher with an imported Word document, you need to give substantially more information. What is the version of Windows, Publisher and Word involved. Is this a Template you are using, and what type of document...

Conditional Max value
Hi, i have 2 columns and i'm trying to calculate the conditonal maximum from column one. These are the columns : 47 7 44 7 71 7 58 7 214 4 22 4 54 7 1 7 45 7 21 7 and i try to find a formula that gives the maximum in column one, where column 2 has value 7 in this case this would be 58 Marc Marc, Use the following array formula: =MAX(IF(B1:B10=7,A1:A10,FALSE)) Change the range references to match your data. This is an array formula, so you must press CTRL+SHIFT+ENTER rather than just ENTER when you first enter the for...

In Excell-2000 where do I set format for date?
In Excell-2000 where do I set format for date? In some tables I have it in dd/mm/yy, but in the others mm/dd/yy How do I change mm/dd/yy to dd/mm/yy? On my w2k Pro, Control Paned date format is set to dd/mm/yy yout it does not seem to affect Excel Hi If you select the cells with the dates in mm/dd/yy and go Format>Cells and in the dialog that opens, go for the Number tab, you can select the Date formats and choose whichever one suits or select Custom and type dd/mm/yy into the Type box. This will change the formatting of your dates. Hope this helps! Richard On 28 Jan, 16:34, &q...

Formatting cells for big numbers and formulas ...without the formu
Hello, I am stuck with this: If I want cells to display like 20-digit numbers in numerical form (that is, not the xxx+EXX way), formatting them as a text is okay. But then I need to add formulas and want the results to be displayed. But as the cells are text formatted, the result in the cell is of course the text of the formula plus the number. So my question is - in a cell I want e.g. "12345678901234567890-sum(A1,B1)" and I want the cell to display the resulting number, not the formula itself. How do I achieve this? The maximum precision for a number is 15 digits according to...

Newbie Help for Custom Number Formats
Hey All, I am working on a spreadsheet that I would like to have it append the inch symbol as part of the number format ("). I figured out that I can add custom suffixes with the use of the quotation marks, but I can't place a quotation mark inside of quotes and get what I need. Is there any way to have Excel do this for me? I know it seems like a small thing to do, but having Excel add this for me would save me a lot of time with formulas and all. Example: 24" Thanks in advance. JB -- joshb416 ------------------------------------------------------------------------ j...

Vlookups and formats
I am using the following formula to pull in the worksheet name into cell B5 =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) The worksheet name is an account number. I then use vlookups to look at B5 as the lookup value to retreive various points of data linked to that specific account number. The problem I have is the formula shown adove brings the sheet name in as text and the vlookup sees it as text. I know this because when I edit the function the returned value for the lookup value is in quotes "1234". If I type i...

Conditional Formatting in VBA in 2007
I have a series of non-contiguous rows that I want to apply conditional formatting to. Right now, my code snip looks like this: With Range(sBegRange, sEndRange) .FormatConditions.Add Type:=xlExpression, _ Formula1:="=" & sCFCell & ">40" .FormatConditions(i).Font.Color = 3 .FormatConditions(i).StopIfTrue = False End With Where sCFCell is a string value for the cell I want evaluated for the formula, and i is an integer that increments for each time I create a new rule (which I'm doing for each row I format). It...

Access 2003 file format advange?
We're upgrading our system from Access 2000 to Access 2003. I'm aware that version 2000 databases can be opened in Access 2003 without conversion, and it did, in fact open, but are there any benefits to converting the database to version 2003? Or, are there reasons why our 2000 database should NOT be converted to 2003? It's hard to beleive that it wouldn't matter one way or the other, and think there must be some advantage to converting to a file format of "2003" if we're using Access 2003. Many thanks in advance. On Tue, 17 Feb 2004 12:31:13 -0500, Si...

Conditional formatting #87
Can I do a conditional formatting on a range if the value in one cell in the range = X in other words can I format the back ground for line 5 if C5 = X Use conditional formating. Select the whole row Set the condition Formula Is =$C$5="X" Choose the Format you want for the cells in that row. -- Regards Roger Govier "ESREI" <anonymous@discussions.microsoft.com> wrote in message news:339b01c51f10$38d61860$a601280a@phx.gbl... > Can I do a conditional formatting on a range if the value > in one cell in the range = X > > in other words can I format the b...

Pasting Excel chart into Word loses formatting
How can I paste an .xls chart into Word and retain the formatting? I tried copying and pasting (with Paste Special and regular Paste) and I am losing the line spacing when the chart displays in Word. Is there any way to gain control over this? Hi, What do you mean by line spacing? what version of Excel are you using? One why is to insert as Excel Chart or Worksheet Object. Another general rule is to create a default size chart in Excel, don't resize it. Then copy and paste it. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Sandra" wrote: ...

Changing My Outlook 2003 PST from 97-2002 to Unicode Format?
What is the best way to get the data from my PST - presently in the older Outlook 97-2002 format - into a new PST in the newer Outlook 2003 Unicose format? (I am using Outlook 2003) I've had Outlook installed as part of MS Office for a few years, starting with, I believe, OL 97. Each time I upgraded to a new version, I just ran the installer and allowed it to use the default. When it sensed, or asked and I responded, the previous version's data it just "took over" the existing PST. Always seemed OK to me. But now that I have had Office/Outlook 2003 installed for over a ...