If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
autofill colour based on date entered
Hi there!
I had posted this question last week and didn't get far without the analysis add-in. I got it now but am missing the instructions on the howto? :P What I would like to do is colour code a spreadsheet for work handedin by staff. I have the top row showing the calendar months from April 2008 to March 2009. The second row shows the due date to the SUpervisor and the due date for review for each month like this: Column A2:A3 = April 2008 Column B2 = May 7, 2008 Column B3 = May 14, 2008 The columns C-Z continue on like this with different due dates for each month. Column A1:A200 = Employee Name Cells entered with a date within the two particular due dates in a month I would like coloured pink. Cells entered with a date after the review due date in a month I would like coloured blue. Cells that have no entry I would like coloured grey to indicate missing work not handed in. Is this possible? Any help is greatly appreciated. Thanks!! |
#2
|
|||
|
|||
autofill colour based on date entered
Your names should be in A4-A100, right?
Select B4:Z100 Choose conditional formatting Choose Formula Is First condition =AND(B$2B4,B$3B4) Format PINK Second Conditon =B$3B4 Format BLUE Third Condition =B4="" Format Grey "Tacrier" wrote: Hi there! I had posted this question last week and didn't get far without the analysis add-in. I got it now but am missing the instructions on the howto? :P What I would like to do is colour code a spreadsheet for work handedin by staff. I have the top row showing the calendar months from April 2008 to March 2009. The second row shows the due date to the SUpervisor and the due date for review for each month like this: Column A2:A3 = April 2008 Column B2 = May 7, 2008 Column B3 = May 14, 2008 The columns C-Z continue on like this with different due dates for each month. Column A1:A200 = Employee Name Cells entered with a date within the two particular due dates in a month I would like coloured pink. Cells entered with a date after the review due date in a month I would like coloured blue. Cells that have no entry I would like coloured grey to indicate missing work not handed in. Is this possible? Any help is greatly appreciated. Thanks!! |
#3
|
|||
|
|||
autofill colour based on date entered
Sorry for not replying right away,
I tried the conditional formatting you had suggested, I modified it (or tried to) because I had put the wrong column labels when describing my spreadsheet. Here is the correct column labels: Column A4:A100 Name of Employee Column B2:C2 April 2008 Column B3 May 7, 2008 Column C3 May 14, 2008 Column D2:E2 May 2008 Column D3 June 9, 2008 Column E3 June 15, 2008 Column F2:G2 July 2008 Column F3 August 5, 2008 Column G3 August 10, 2008 etc The colour coding of cells would be in B4:Z100 Pink : For entered date that falls between the two dates for the particular month. Blue : For entered date that falls after the 2nd date for the particular month Grey : For no date entered to indicate no work handed in I hope I did not make it too confusing, any/all suggestions are greatly appreciated. Thanks "Sheeloo" wrote: Your names should be in A4-A100, right? Select B4:Z100 Choose conditional formatting Choose Formula Is First condition =AND(B$2B4,B$3B4) Format PINK Second Conditon =B$3B4 Format BLUE Third Condition =B4="" Format Grey "Tacrier" wrote: Hi there! I had posted this question last week and didn't get far without the analysis add-in. I got it now but am missing the instructions on the howto? :P What I would like to do is colour code a spreadsheet for work handedin by staff. I have the top row showing the calendar months from April 2008 to March 2009. The second row shows the due date to the SUpervisor and the due date for review for each month like this: Column A2:A3 = April 2008 Column B2 = May 7, 2008 Column B3 = May 14, 2008 The columns C-Z continue on like this with different due dates for each month. Column A1:A200 = Employee Name Cells entered with a date within the two particular due dates in a month I would like coloured pink. Cells entered with a date after the review due date in a month I would like coloured blue. Cells that have no entry I would like coloured grey to indicate missing work not handed in. Is this possible? Any help is greatly appreciated. Thanks!! |
#4
|
|||
|
|||
autofill colour based on date entered
Try
Select B4:Z100 Choose conditional formatting Choose Formula Is First condition =AND(B$2B4,C$2B4) Format PINK Second Conditon =C$2B4 Format BLUE Third Condition =B4="" Format Grey Let me know what happens... "Tacrier" wrote: Sorry for not replying right away, I tried the conditional formatting you had suggested, I modified it (or tried to) because I had put the wrong column labels when describing my spreadsheet. Here is the correct column labels: Column A4:A100 Name of Employee Column B2:C2 April 2008 Column B3 May 7, 2008 Column C3 May 14, 2008 Column D2:E2 May 2008 Column D3 June 9, 2008 Column E3 June 15, 2008 Column F2:G2 July 2008 Column F3 August 5, 2008 Column G3 August 10, 2008 etc The colour coding of cells would be in B4:Z100 Pink : For entered date that falls between the two dates for the particular month. Blue : For entered date that falls after the 2nd date for the particular month Grey : For no date entered to indicate no work handed in I hope I did not make it too confusing, any/all suggestions are greatly appreciated. Thanks "Sheeloo" wrote: Your names should be in A4-A100, right? Select B4:Z100 Choose conditional formatting Choose Formula Is First condition =AND(B$2B4,B$3B4) Format PINK Second Conditon =B$3B4 Format BLUE Third Condition =B4="" Format Grey "Tacrier" wrote: Hi there! I had posted this question last week and didn't get far without the analysis add-in. I got it now but am missing the instructions on the howto? :P What I would like to do is colour code a spreadsheet for work handedin by staff. I have the top row showing the calendar months from April 2008 to March 2009. The second row shows the due date to the SUpervisor and the due date for review for each month like this: Column A2:A3 = April 2008 Column B2 = May 7, 2008 Column B3 = May 14, 2008 The columns C-Z continue on like this with different due dates for each month. Column A1:A200 = Employee Name Cells entered with a date within the two particular due dates in a month I would like coloured pink. Cells entered with a date after the review due date in a month I would like coloured blue. Cells that have no entry I would like coloured grey to indicate missing work not handed in. Is this possible? Any help is greatly appreciated. Thanks!! |
#5
|
|||
|
|||
autofill colour based on date entered
It made all of the empty cells grey (yay!) and all of the entered cells blue
(boo) lol Don't know what else to do except manually colour the cells? "Sheeloo" wrote: Try Select B4:Z100 Choose conditional formatting Choose Formula Is First condition =AND(B$2B4,C$2B4) Format PINK Second Conditon =C$2B4 Format BLUE Third Condition =B4="" Format Grey Let me know what happens... "Tacrier" wrote: Sorry for not replying right away, I tried the conditional formatting you had suggested, I modified it (or tried to) because I had put the wrong column labels when describing my spreadsheet. Here is the correct column labels: Column A4:A100 Name of Employee Column B2:C2 April 2008 Column B3 May 7, 2008 Column C3 May 14, 2008 Column D2:E2 May 2008 Column D3 June 9, 2008 Column E3 June 15, 2008 Column F2:G2 July 2008 Column F3 August 5, 2008 Column G3 August 10, 2008 etc The colour coding of cells would be in B4:Z100 Pink : For entered date that falls between the two dates for the particular month. Blue : For entered date that falls after the 2nd date for the particular month Grey : For no date entered to indicate no work handed in I hope I did not make it too confusing, any/all suggestions are greatly appreciated. Thanks "Sheeloo" wrote: Your names should be in A4-A100, right? Select B4:Z100 Choose conditional formatting Choose Formula Is First condition =AND(B$2B4,B$3B4) Format PINK Second Conditon =B$3B4 Format BLUE Third Condition =B4="" Format Grey "Tacrier" wrote: Hi there! I had posted this question last week and didn't get far without the analysis add-in. I got it now but am missing the instructions on the howto? :P What I would like to do is colour code a spreadsheet for work handedin by staff. I have the top row showing the calendar months from April 2008 to March 2009. The second row shows the due date to the SUpervisor and the due date for review for each month like this: Column A2:A3 = April 2008 Column B2 = May 7, 2008 Column B3 = May 14, 2008 The columns C-Z continue on like this with different due dates for each month. Column A1:A200 = Employee Name Cells entered with a date within the two particular due dates in a month I would like coloured pink. Cells entered with a date after the review due date in a month I would like coloured blue. Cells that have no entry I would like coloured grey to indicate missing work not handed in. Is this possible? Any help is greatly appreciated. Thanks!! |
Thread Tools | |
Display Modes | |
|
|