Is it possible to start with the value "A" in a cell and then add the same value in another cell while automatically removing or hiding the original "A"? In other words, I'd like to start with a group of values on one line and then as I use the values on another line, I would like the original values disappear or hide. This would be helpful in determining what assignments I've used verses what assignments not used. Any direction would be much appreciated.
Monthly work schedule, disappearing assignments
- Thread starter RadMan
- Start date
Hi RadMan,
Not sure I understand your requirement exactly -- but nonetheless I feel pretty confident that the answer will be "yes"
.
If you mean something like this:
[pre]
[/pre]
then you could use a conditional formatting formula to hide the appropriate value in row 1, or if you need formulas that references value in row 1 to also see the cells as empty, you could use cell formulas in row 1 to achieve that.
For the conditional formatting approach, this will achieve the same as the sample above:
1. Highlight the cells on row 1 that will hold your set of values, making sure that the first cell in that range is the active cell (highlight from left to right to achieve that effect, or highlight as you like, then ctrl-click in cell A1).
2. Start the conditional formatting rules manager (Format/Conditional Formatting[Alt-O,D] or Home/Styles/Conditional Formatting[Alt-H,L,R])
3. New Rule
4. Use a Formula...
5. =NOT(ISERROR(MATCH(A1,$A$2:$E$2,0)))
6. Format
7. Number/Custom
8. ;;;
Asa
Not sure I understand your requirement exactly -- but nonetheless I feel pretty confident that the answer will be "yes"
.If you mean something like this:
[pre]
Code:
| ___A___ ___B___ ___C___ ___D___ ___E___
1 a g i l e
becoming this:
| ___A___ ___B___ ___C___ ___D___ ___E___
1 a i l
2 e gthen you could use a conditional formatting formula to hide the appropriate value in row 1, or if you need formulas that references value in row 1 to also see the cells as empty, you could use cell formulas in row 1 to achieve that.
For the conditional formatting approach, this will achieve the same as the sample above:
1. Highlight the cells on row 1 that will hold your set of values, making sure that the first cell in that range is the active cell (highlight from left to right to achieve that effect, or highlight as you like, then ctrl-click in cell A1).
2. Start the conditional formatting rules manager (Format/Conditional Formatting[Alt-O,D] or Home/Styles/Conditional Formatting[Alt-H,L,R])
3. New Rule
4. Use a Formula...
5. =NOT(ISERROR(MATCH(A1,$A$2:$E$2,0)))
6. Format
7. Number/Custom
8. ;;;
Asa
Thank you for the reply.
I'm looking for the something like this:
Month
Date 1 2 3 4 5 6 7 8 9 10 11 12...
Employee Day M T W TH F S S M T W TH F...
Jim A A A A A P P P P P
Todd P P P P P A A A A A
Kim S S S S S T T T T T
Tom T T T T T K K K K K
Jay K K K K K S S S S S
Jane
ect.
______________________________________________________________________________
S P A A P
K T S K
T
I'd like to start with all the values below the line. As I use them above they disappear below. Or if I remove them above it reappears below. Does that make sense? It helps me know what assignments I'm missing for any given day.
This doesn't look right when it posted. The values should line up with the dates and days next to each employee.
I'm looking for the something like this:
Month
Date 1 2 3 4 5 6 7 8 9 10 11 12...
Employee Day M T W TH F S S M T W TH F...
Jim A A A A A P P P P P
Todd P P P P P A A A A A
Kim S S S S S T T T T T
Tom T T T T T K K K K K
Jay K K K K K S S S S S
Jane
ect.
______________________________________________________________________________
S P A A P
K T S K
T
I'd like to start with all the values below the line. As I use them above they disappear below. Or if I remove them above it reappears below. Does that make sense? It helps me know what assignments I'm missing for any given day.
This doesn't look right when it posted.
The values should line up with the dates and days next to each employee.NARAYANK991
Excel Ninja
Hi ,
Can you clarify the following :
1. Below the line , you have 3 rows of values ; how do these lines of values correspond with the employees ? Suppose for instance the values T T T T T are removed from the employee named Kim , where will these appear below the line ?
2. In the values below the line , why are there repetitions ? What does S P A A P signify , why not just the values S P A ?
Narayan
Can you clarify the following :
1. Below the line , you have 3 rows of values ; how do these lines of values correspond with the employees ? Suppose for instance the values T T T T T are removed from the employee named Kim , where will these appear below the line ?
2. In the values below the line , why are there repetitions ? What does S P A A P signify , why not just the values S P A ?
Narayan
I have worked up a sample sheet that I think does close to what you are looking for.
Employee Assignment Schedule.xlsx
I threw in a couple additional formatting features. Notes to right side of sheet.
Click the download button after following the above link. Excel 2007+ format. Let me know if you need it to be compatible with earlier versions.
The main formula that does what I think you asked for is as follows (for future readers of this thread if my link no longer works)... This is a conditional formatting rule for the cells that contain the available assignments, below the area where assignments are entered (below the line). It assumes one available assignment per cell, in a vertical orientation: I use C19 (top left of available assignment area, day 1) to AG30 (bottom right of said area, day 31). Rows 4 to 18 are where employee assignments are entered.
More in sample file.
Let me know if this demonstrates what you need and if you understand how it works. I am happy to explain --- I consider the lesson more important than the solution.
Asa
p.s. Narayan: thanks! Funny, I was just about to post when you did...
Employee Assignment Schedule.xlsx
I threw in a couple additional formatting features. Notes to right side of sheet.
Click the download button after following the above link. Excel 2007+ format. Let me know if you need it to be compatible with earlier versions.
The main formula that does what I think you asked for is as follows (for future readers of this thread if my link no longer works)... This is a conditional formatting rule for the cells that contain the available assignments, below the area where assignments are entered (below the line). It assumes one available assignment per cell, in a vertical orientation: I use C19 (top left of available assignment area, day 1) to AG30 (bottom right of said area, day 31). Rows 4 to 18 are where employee assignments are entered.
Code:
=NOT(ISERROR(MATCH(C19,C$4:C$18,0)))
Use the same custom number format I metnioned in my first post:
;;;More in sample file.
Let me know if this demonstrates what you need and if you understand how it works. I am happy to explain --- I consider the lesson more important than the solution.
Asa
p.s. Narayan: thanks! Funny, I was just about to post when you did...
NARAYANK991
Excel Ninja
Hi Asa ,
Thanks for your worksheet.
If there are 6 employees and only 5 assignments , does it mean one employee cannot be assigned ?
Narayan
Thanks for your worksheet.
If there are 6 employees and only 5 assignments , does it mean one employee cannot be assigned ?
Narayan
Yes. I set the data validation rule to prevent using an assignment more than once or entering an assignment code not on the list of available assignments. To change that behavior, you could change the data validation rule.
The list of available assignments with this spreadsheet design can be unique on a per-day basis though, so if there were different numbers of jobs on different days you could vary the available assignment list to allow as many (different) employee assignments as desired.
I imagine that this could be used as a form of scheduling where not all employees are needed every day.
Asa
The list of available assignments with this spreadsheet design can be unique on a per-day basis though, so if there were different numbers of jobs on different days you could vary the available assignment list to allow as many (different) employee assignments as desired.
I imagine that this could be used as a form of scheduling where not all employees are needed every day.
Asa
NARAYANK991
Excel Ninja
OK. Thanks.
Narayan
Narayan
Just woke up to see if anyone posted. Thank you so much. I'll study it and see if i can figure out what you did and if i can use it. But so far it looks like you understand exactly what I'm looking for. Thank you for the extra's(bold line between Sa and Su)
I've used formatted excel spreadsheets for various purposes over the last 2 years. However, I'm not very familiar with formatting one myself. I'm good at taking something and making it work for my purposes.
How did you create that link so i can share with you the format I've been using? It's pretty slick. But there are parts of it I like and parts that i don't like. With the info you're sharing with me, I should be able to make it quite useful. Just shoot me the steps to create that link, if you don't mind. I'm pretty sure I can figure it out. Otherwise, you have been a huge help.
I really enjoy working with spreadsheets. I like dissecting them to see how they work. I can't wait to study your information. Thanks again.
Mark
I've used formatted excel spreadsheets for various purposes over the last 2 years. However, I'm not very familiar with formatting one myself. I'm good at taking something and making it work for my purposes.
How did you create that link so i can share with you the format I've been using? It's pretty slick. But there are parts of it I like and parts that i don't like. With the info you're sharing with me, I should be able to make it quite useful. Just shoot me the steps to create that link, if you don't mind. I'm pretty sure I can figure it out. Otherwise, you have been a huge help.
I really enjoy working with spreadsheets. I like dissecting them to see how they work. I can't wait to study your information. Thanks again.
Mark
I just downloaded it and started playing with it. It's much better then what i'm use to. You can still tell me how to create a link, as i'm sure i'm going to stick around this forum. But i don't need to show you what I've been using. I'll use this one.
Is there a way to automatically color code the day "Cell" for the first day of the pay period which is Sunday biweekly? Thanks for all your help.
Mark
Is there a way to automatically color code the day "Cell" for the first day of the pay period which is Sunday biweekly? Thanks for all your help.
Mark
Hi Mark!
Glad you like it! I'm happy to help you with your wish list, although my time is a bit limited this week.. in other words, I may be slow in replying.
To create a link, you can just paste a URL into your reply and it will be clickable. To have link text you have to use some HTML codes, but you needn't worry about that. I uploaded the file to Google Docs, but any file sharing site works.
Sure. You could use another conditional formatting rule.
When you use a formula for a conditional format rule, you can use nearly any formula you can use in a cell in a spreadsheet, but you want the result of the formula to be TRUE or FALSE. When the result of the formula is TRUE, the format you select gets applied.
The formula
In a conditional format rule will always apply the formatting, although that's not very useful.
A conditional format formula can have both relative and absolute cell references. When you create a conditional format rule for a range of cells, you enter the formula with cell references as they should appear in the top left cell in the selected range. Absolute references will stay the same for all the other cells in the range, and relative references will change/be evaluated differently for the other cells, in exactly the same way they would were you to enter that same formula in those cells on the spreadsheet.
I recommend writing a formula that can determine whether a given day is the first day of the pay period in a cell on the spreadsheet, first. It sounds like the relevant information is the date and/or day of the week. You will have to do some calculations.
Once you have the right formula, and it results in TRUE on your spreadsheet, on the appropriate days, when in any row while in columns C:AG (or whatever the same columns are that your conditional formatting is intended for), you can just copy that formula as it looks in the first of those columns and paste it into the formula entry for a conditional format.
If you look up "date and time functions" in Excel help you might get some ideas. Some additional info that could be useful:
Dates and times are stored internally as a "date serial number", simply a never-ending count of days since 12:00 AM on January 1, 1900, or in some cases, 1904 (which are considered serial number 1).
This means you can do ordinary arithmetic with dates and times, and the result will be in terms of days.
The DATE(y,m,d) function converts a year, month, and day to a date (date serial). WEEKDAY(date[,return type]) returns the day of the week of a date serial, represented numerically (1 being Sunday by default). YEAR(date), MONTH(date) and DAY(date) extract that info back out of a date. To determine the number of weeks between dates, you could take the difference between the two dates and divide by 7. Divide by 14 to count how many two week periods. To determine if it's been exactly two weeks or a multiple thereof, you wouldn't need to know how many periods between the dates, but you would want to make sure there is no remainder/fraction after dividing by the number of days in a period. For that, you can use the MOD(number, divisor) function, which returns the remainder of number/divisor, or you can check if (number/divisor)=INT(number/divisor).
See if you can come up with a formula yourself, if you get stuck, let me know the sticking point.
It's true that my assignments change given the day of the week. Is it possible to have the different assignments follow the day of the week when i change the month?>
Yes. But that suggests entering the daily available assignments somewhere else besides at the bottom of the schedule, where you will then enter your assignments for just 7 days, instead of for every day in the month. Then you could use formulas in the "Available Assignments" area to lookup the assignments for that day of the week using VLOOKUP, INDEX and MATCH functions, or some other lookup method.
I was playing some more with the sample file since I first uploaded it, mainly because I was inspired by a recent discussion on this forum about advanced use of lookup formulas to create sorted lists, and by the fact that your sample data that you posted appeared to show the available assignments left for any day without any gaps between them, immediately under the line. With our spreadsheet I created a separate table of available assignments, and came up with a formula for the Available Assignments area that does just that. Instead of using conditional formatting to hide used assignments, they simply are not included in the list anymore, and the list will change as you add/delete assignments. Since unavailable assignments are actually not on the avail. assignments area of the spreadsheet anymore, I was able to change the data validation rule for the cells where you enter the assignments from using a formula to using a list of available values (the same list you see in the avail. assignments area). This places the same restrictions on what you can enter, but has a bonus of providing a pop up list/listbox next to each cell as you click/move between them that you can select from instead of typing the assignment code, if you wish. Now, it currently uses a single list of available assignments and applies them on every day of the month, but I anticipated that it would require additional modification to be more flexible than that.
I haven't had time to make sure what I described all works without hitches, so I hesitate to share it with you until I do.. Let me know if you are interested in seeing that, or if you prefer to work this out on your own/with support.
Some things I wondered about changes in available assignments: (1) might they change from time to time.... including in the middle of a month, as your needs change (2) is there a regular, predictable schedule (you've basically answered that with your question - yes I think) (3) might you still want to override the usual plan?
The answers to those questions help to decide the best way to achieve all your needs.
Yes
That message is part of the data validation rule. You can only have one data validation rule for any given cell, but you can have that one rule check any number of conditions. Your conditional formatting formula for identifying pay period changes should be helpful in developing a modification to the data validation rule to achieve what you want. In fact, since you need to identify pay periods for both the data validation purposes and conditional formatting, I think it might be a good idea to put the formula to determine pay period or pay period changes in a row of it's own on the spreadsheet (the row could be hidden) and refer to it with your conditional formatting and data validation formulas both. The data validation solution could probably be done with a COUNTIFS formula.
Provided C31:AG31 contains a unique value, specifying the pay period...
C4:AG4 and other rows below that contain the area to apply data validation...
The above formula is a valid data validation rule intended for a range with top left cell C4. It will return true only if 10 or fewer matches between the existence of assignments on the current row, C (given employee) and the same pay period as the current column's, as found in row 31.
The current data validation rule
checks two conditions, see if you can see how to add the third. The first trial will be calculating the pay period, though!
It's OK, ask as much as you like! In some cases you may prefer to start a new topic for different problems, but this works for me for now. Feel free to answer some questions yourself, if you are so inclined.
Welcome to Chandoo forums! Personally, I posted here a month ago to get help with a problem of my own, and quickly found I liked this small, friendly, and a bit laid back community. Make sure to check out, if you haven't already, the rest of the site. Chandoo himself is a good teacher (based on my experience reading his articles and his occasional forum responses), has perhaps hundred of articles and blog posts up, and offers online classes.
Oh! By the way, to have your text aligned, as you tried to do with your sample data, you can use the backquote (on the tilde key). Placing a backquote before and after the text will use a monospace font. For some reason, it doesn't retain leading spaces on the first line of text, but thereafter it does. For other formatting of forum posts, you can use any of a few html-style markup codes. They're listed below the text entry box when you type a post. Took a bit of getting used to for me You can edit your posts for a bit after you post them (I think up to an hour), and then they are fixed for perpetuity.
Asa
Glad you like it! I'm happy to help you with your wish list, although my time is a bit limited this week.. in other words, I may be slow in replying.
To create a link, you can just paste a URL into your reply and it will be clickable. To have link text you have to use some HTML codes, but you needn't worry about that. I uploaded the file to Google Docs, but any file sharing site works.
Sure. You could use another conditional formatting rule.
When you use a formula for a conditional format rule, you can use nearly any formula you can use in a cell in a spreadsheet, but you want the result of the formula to be TRUE or FALSE. When the result of the formula is TRUE, the format you select gets applied.
The formula
Code:
=TRUEIn a conditional format rule will always apply the formatting, although that's not very useful.
A conditional format formula can have both relative and absolute cell references. When you create a conditional format rule for a range of cells, you enter the formula with cell references as they should appear in the top left cell in the selected range. Absolute references will stay the same for all the other cells in the range, and relative references will change/be evaluated differently for the other cells, in exactly the same way they would were you to enter that same formula in those cells on the spreadsheet.
I recommend writing a formula that can determine whether a given day is the first day of the pay period in a cell on the spreadsheet, first. It sounds like the relevant information is the date and/or day of the week. You will have to do some calculations.
Once you have the right formula, and it results in TRUE on your spreadsheet, on the appropriate days, when in any row while in columns C:AG (or whatever the same columns are that your conditional formatting is intended for), you can just copy that formula as it looks in the first of those columns and paste it into the formula entry for a conditional format.
If you look up "date and time functions" in Excel help you might get some ideas. Some additional info that could be useful:
Dates and times are stored internally as a "date serial number", simply a never-ending count of days since 12:00 AM on January 1, 1900, or in some cases, 1904 (which are considered serial number 1).
This means you can do ordinary arithmetic with dates and times, and the result will be in terms of days.
The DATE(y,m,d) function converts a year, month, and day to a date (date serial). WEEKDAY(date[,return type]) returns the day of the week of a date serial, represented numerically (1 being Sunday by default). YEAR(date), MONTH(date) and DAY(date) extract that info back out of a date. To determine the number of weeks between dates, you could take the difference between the two dates and divide by 7. Divide by 14 to count how many two week periods. To determine if it's been exactly two weeks or a multiple thereof, you wouldn't need to know how many periods between the dates, but you would want to make sure there is no remainder/fraction after dividing by the number of days in a period. For that, you can use the MOD(number, divisor) function, which returns the remainder of number/divisor, or you can check if (number/divisor)=INT(number/divisor).
See if you can come up with a formula yourself, if you get stuck, let me know the sticking point.
It's true that my assignments change given the day of the week. Is it possible to have the different assignments follow the day of the week when i change the month?>
Yes. But that suggests entering the daily available assignments somewhere else besides at the bottom of the schedule, where you will then enter your assignments for just 7 days, instead of for every day in the month. Then you could use formulas in the "Available Assignments" area to lookup the assignments for that day of the week using VLOOKUP, INDEX and MATCH functions, or some other lookup method.
I was playing some more with the sample file since I first uploaded it, mainly because I was inspired by a recent discussion on this forum about advanced use of lookup formulas to create sorted lists, and by the fact that your sample data that you posted appeared to show the available assignments left for any day without any gaps between them, immediately under the line. With our spreadsheet I created a separate table of available assignments, and came up with a formula for the Available Assignments area that does just that. Instead of using conditional formatting to hide used assignments, they simply are not included in the list anymore, and the list will change as you add/delete assignments. Since unavailable assignments are actually not on the avail. assignments area of the spreadsheet anymore, I was able to change the data validation rule for the cells where you enter the assignments from using a formula to using a list of available values (the same list you see in the avail. assignments area). This places the same restrictions on what you can enter, but has a bonus of providing a pop up list/listbox next to each cell as you click/move between them that you can select from instead of typing the assignment code, if you wish. Now, it currently uses a single list of available assignments and applies them on every day of the month, but I anticipated that it would require additional modification to be more flexible than that.
I haven't had time to make sure what I described all works without hitches, so I hesitate to share it with you until I do.. Let me know if you are interested in seeing that, or if you prefer to work this out on your own/with support.
Some things I wondered about changes in available assignments: (1) might they change from time to time.... including in the middle of a month, as your needs change (2) is there a regular, predictable schedule (you've basically answered that with your question - yes I think) (3) might you still want to override the usual plan?
The answers to those questions help to decide the best way to achieve all your needs.
Yes
That message is part of the data validation rule. You can only have one data validation rule for any given cell, but you can have that one rule check any number of conditions. Your conditional formatting formula for identifying pay period changes should be helpful in developing a modification to the data validation rule to achieve what you want. In fact, since you need to identify pay periods for both the data validation purposes and conditional formatting, I think it might be a good idea to put the formula to determine pay period or pay period changes in a row of it's own on the spreadsheet (the row could be hidden) and refer to it with your conditional formatting and data validation formulas both. The data validation solution could probably be done with a COUNTIFS formula.
Code:
=COUNTIFS($C$31:$AG$31,C$31,$C4:$AG4,"<>")<=10Provided C31:AG31 contains a unique value, specifying the pay period...
C4:AG4 and other rows below that contain the area to apply data validation...
The above formula is a valid data validation rule intended for a range with top left cell C4. It will return true only if 10 or fewer matches between the existence of assignments on the current row, C (given employee) and the same pay period as the current column's, as found in row 31.
The current data validation rule
Code:
=AND(COUNTIF(C$4:C$18,C4)=1,NOT(ISERROR(MATCH(C4,C$19:C$30,0))))checks two conditions, see if you can see how to add the third. The first trial will be calculating the pay period, though!
It's OK, ask as much as you like! In some cases you may prefer to start a new topic for different problems, but this works for me for now. Feel free to answer some questions yourself, if you are so inclined.
Welcome to Chandoo forums! Personally, I posted here a month ago to get help with a problem of my own, and quickly found I liked this small, friendly, and a bit laid back community. Make sure to check out, if you haven't already, the rest of the site. Chandoo himself is a good teacher (based on my experience reading his articles and his occasional forum responses), has perhaps hundred of articles and blog posts up, and offers online classes.
Oh! By the way, to have your text aligned, as you tried to do with your sample data, you can use the backquote (on the tilde key). Placing a backquote before and after the text will use a monospace font. For some reason, it doesn't retain leading spaces on the first line of text, but thereafter it does. For other formatting of forum posts, you can use any of a few html-style markup codes. They're listed below the text entry box when you type a post. Took a bit of getting used to for me
You can edit your posts for a bit after you post them (I think up to an hour), and then they are fixed for perpetuity.Asa
Asa, I really appreciate your help. This is way over my head. But that's ok. I really want to figure this out. I spend a lot of time working and then taking my kids to different activities. I'd love to take one of Chandoo's classes but I dont think i would follow through with it if it didn't pertain to what I need the instruction for. I'm motivated to work this assignment spreadsheet. I'll give this a go.
'(1) might they change from time to time.... with the including in the middle of a month, as your needs change'
"Yes, my assignments changes for holidays. They become the same assignments as weekends.
'(2) is there a regular, predictable schedule (you've basically answered that with your question - yes I think)(3) might you still want to override the usual plan?'
"Some employee's have set schedules, some rotate weekly, and some are random. I have 60 + employees. With set schdueles or otherwise, there are days that i have to add paid leave requests, Jury duty, ect.
I'd like to tackle my biggest hurdle first. I'd like the assignments to follow the days of the week while adding a color to the Sa Su column.
First question. I understand the need for formulas. But where do i put it. I can't find the formula that makes the assignments disappear.
Otherwise, i will have to reread what you wrote above many times while combing over what you've provided.
Thanks again.
Mark
Yes,
'(1) might they change from time to time.... with the including in the middle of a month, as your needs change'
"Yes, my assignments changes for holidays. They become the same assignments as weekends.
'(2) is there a regular, predictable schedule (you've basically answered that with your question - yes I think)(3) might you still want to override the usual plan?'
"Some employee's have set schedules, some rotate weekly, and some are random. I have 60 + employees. With set schdueles or otherwise, there are days that i have to add paid leave requests, Jury duty, ect.
I'd like to tackle my biggest hurdle first. I'd like the assignments to follow the days of the week while adding a color to the Sa Su column.
First question. I understand the need for formulas. But where do i put it. I can't find the formula that makes the assignments disappear.
Otherwise, i will have to reread what you wrote above many times while combing over what you've provided.
Thanks again.
Mark
Yes,
I thought i'd share with you what i'm currently using as a schedule.
This is the link for the potential assignment schedule for January as i have it laid out so far.
https://docs.google.com/viewer?a=v&pid=gmail&attid=0.1&thid=1338bb99985631ba&mt=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&url=https://mail.google.com/mail/?ui%3D2%26ik%3D33b7a5fb92%26view%3Datt%26th%3D1338bb99985631ba%26attid%3D0.1%26disp%3Dsafe%26realattid%3Df_gut98lig0%26zw&sig=AHIEtbQtHYp_wbJ3Ca_6T6HsxydO_Ipz8Q
Here is the link that shows what i used in October.
https://docs.google.com/viewer?a=v&pid=gmail&attid=0.2&thid=1338bb99985631ba&mt=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&url=https://mail.google.com/mail/?ui%3D2%26ik%3D33b7a5fb92%26view%3Datt%26th%3D1338bb99985631ba%26attid%3D0.2%26disp%3Dsafe%26realattid%3Df_gut98uvg1%26zw&sig=AHIEtbRSi0CN9b1lFnlFRjBSR4Dbsd8ZKg
Pretty long url I don't know the HTML code.
Mark
This is the link for the potential assignment schedule for January as i have it laid out so far.
https://docs.google.com/viewer?a=v&pid=gmail&attid=0.1&thid=1338bb99985631ba&mt=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&url=https://mail.google.com/mail/?ui%3D2%26ik%3D33b7a5fb92%26view%3Datt%26th%3D1338bb99985631ba%26attid%3D0.1%26disp%3Dsafe%26realattid%3Df_gut98lig0%26zw&sig=AHIEtbQtHYp_wbJ3Ca_6T6HsxydO_Ipz8Q
Here is the link that shows what i used in October.
https://docs.google.com/viewer?a=v&pid=gmail&attid=0.2&thid=1338bb99985631ba&mt=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&url=https://mail.google.com/mail/?ui%3D2%26ik%3D33b7a5fb92%26view%3Datt%26th%3D1338bb99985631ba%26attid%3D0.2%26disp%3Dsafe%26realattid%3Df_gut98uvg1%26zw&sig=AHIEtbRSi0CN9b1lFnlFRjBSR4Dbsd8ZKg
Pretty long url
I don't know the HTML code.Mark
Hi Mark,
Those links don't work for me -- I just get an error. If you haven't already, you'll need to tell Google to share your file before others can access it.
My approach with Google Docs is: first, when uploading the file, choose not to convert to Google Docs format, unless the file is very simple, as not all features will work after the conversion. Then after uploading, Right click the file within Google Docs and choose "Share". To generate a link that you can post on a forum or share with a group of people, click "Change" under the "Who has access" heading. Then choose the option you prefer -- perhaps "Anyone with the link". Hit "Save" and you will see a URL for sharing that you can copy/paste.
To see the formula that makes the available assignments disappear, follow step 2 from my first post in this thread where I describe how to pull up the Conditional Formatting Rules Manager. At the top of that window, it should say "Show formatting rules for" followed by a listbox of choices. Choose "This Worksheet" to see all conditional formatting rules for the worksheet.
There are two rules, and the one listed for the range where the assignments disappear (C19:AG30) is the one that does the disappearing act. This is the formula:
which hides the cell's value.
~~~
The other conditional format rule is the one that puts the dashed line between weeks.
Asa
Those links don't work for me -- I just get an error. If you haven't already, you'll need to tell Google to share your file before others can access it.
My approach with Google Docs is: first, when uploading the file, choose not to convert to Google Docs format, unless the file is very simple, as not all features will work after the conversion. Then after uploading, Right click the file within Google Docs and choose "Share". To generate a link that you can post on a forum or share with a group of people, click "Change" under the "Who has access" heading. Then choose the option you prefer -- perhaps "Anyone with the link". Hit "Save" and you will see a URL for sharing that you can copy/paste.
To see the formula that makes the available assignments disappear, follow step 2 from my first post in this thread where I describe how to pull up the Conditional Formatting Rules Manager. At the top of that window, it should say "Show formatting rules for" followed by a listbox of choices. Choose "This Worksheet" to see all conditional formatting rules for the worksheet.
There are two rules, and the one listed for the range where the assignments disappear (C19:AG30) is the one that does the disappearing act. This is the formula:
Code:
=NOT(ISERROR(MATCH(C19,C$4:C$18,0)))
If you edit that rule, you can change the formula and/or then hit the "Format" button to change what formatting is applied by the rule. It applies the custom number format ;;;~~~
The other conditional format rule is the one that puts the dashed line between weeks.
Asa
Try these.
January schedule.xlsx
October schedule.xlsx
Thanks for the tips, I figured out the HTML code. Now for the formula's.
January schedule.xlsx
October schedule.xlsx
Thanks for the tips, I figured out the HTML code. Now for the formula's.
Hi Asa,
After studying your comments, I've decided against having my assignments adjust with the days of the week. I need to show a full months assignments at a time.
I've also decided against creating an error message if my assignments add up to more then 10 in a pay period because my assignments X1, X2, ect. are "regular" days off. Unless i can omit some "X" assignments, i wont be able to indicate unusual "regular" days off.
I'm going to focus on creating a conditional formatting rule that colors 1/1/2012 and every 14th day thereafter.
From your comments above, 1. I need to do some calculations. 2. write a formula. 3. then copy that formula in the first of those columns and paste it into the formula entry for a conditional format.
I figured out how to color every Sunday. Now i need to figure out a formula that colors 1/1/2012 and every 14th day thereafter. Correct? Where would i find examples of formulas like that?
Assignment-Schedule-EverySunday-colored.xlsx
Thanks
Mark
After studying your comments, I've decided against having my assignments adjust with the days of the week. I need to show a full months assignments at a time.
I've also decided against creating an error message if my assignments add up to more then 10 in a pay period because my assignments X1, X2, ect. are "regular" days off. Unless i can omit some "X" assignments, i wont be able to indicate unusual "regular" days off.
I'm going to focus on creating a conditional formatting rule that colors 1/1/2012 and every 14th day thereafter.
From your comments above, 1. I need to do some calculations. 2. write a formula. 3. then copy that formula in the first of those columns and paste it into the formula entry for a conditional format.
I figured out how to color every Sunday. Now i need to figure out a formula that colors 1/1/2012 and every 14th day thereafter. Correct? Where would i find examples of formulas like that?
Assignment-Schedule-EverySunday-colored.xlsx
Thanks
Mark
Hey, Mark,
I like your coloring! You might want to make a little change to your Saturday and Sunday color rules. I had the Sunday rule starting on the second day of the month -- column D -- because I thought there was no point in drawing a dashed line to the left of day 1, since the line was intended to separate Saturday and Sunday. Now that you have a background color though, it makes sense to apply those rules for every day of the month. To do that, just change the "Applies to" range on those rules to
remainder of days' difference/21=0[/code]? [=MOD(day's difference,21)=0[/code]]
=TRUE[/code]/FALSE[/code]
We use an absolute reference to cell A1 [$A$1[/code]] for month and year, since we always want to refer to cell A1 for those, we don't want it to look in other rows or columns as it evaluates the format for each cell.
We use a relative reference to the column with an absolute reference to the row [C$2[/code]] to retrieve the day of the month for the current column, which is always in row 2.
Well, remember my answer to "is it possible" is almost always "Yes." It would be possible, if I understand you correctly, to only have an error if there are more than 10 assignments other than X1, X2, etc. But we can come back to that if you want to pursue it.
I haven't had a chance to look closely at your other sample files, but you certainly have a lot of data consolidated there. You could "automate" various logic. For example, you could have a conditional format rule to highlight holidays. You could modify the data validation rule to prevent scheduling employees if they have leave scheduled, or if it's a holiday. You might want to change the data validation rule's behavior to allow you to schedule people anyway, but just warn you, so you don't end up trapped by the computer's logic when you just need to get things scheduled as you know they should be.
Chinese?
Asa
I like your coloring! You might want to make a little change to your Saturday and Sunday color rules. I had the Sunday rule starting on the second day of the month -- column D -- because I thought there was no point in drawing a dashed line to the left of day 1, since the line was intended to separate Saturday and Sunday. Now that you have a background color though, it makes sense to apply those rules for every day of the month. To do that, just change the "Applies to" range on those rules to
Code:
=$C$2:$AG$30 and change the formula for those rules to start with [code]=C$3 instead of [code]=D$3.
Well, I could give you a formula to select every 14th day going backward and forward from 1/1/2012, for formatting. I don't want to make it tooooo easy for you though! But, since you ask.. here's an example that I think will select every 21st day going backward and forward from 6/1/2012 :D...
[code]=MOD(DATE(2012,6,1)-DATE(YEAR($A$1),MONTH($A$1),C$2),21)=0
This works like this:
date1=[code]June 1, 2012 [serial day number for that date]
date2=[code]current column's date [serial number]
days' difference=[code]date1-date2remainder of days' difference/21=0[/code]? [=MOD(day's difference,21)=0[/code]]
=TRUE[/code]/FALSE[/code]
We use an absolute reference to cell A1 [$A$1[/code]] for month and year, since we always want to refer to cell A1 for those, we don't want it to look in other rows or columns as it evaluates the format for each cell.
We use a relative reference to the column with an absolute reference to the row [C$2[/code]] to retrieve the day of the month for the current column, which is always in row 2.
Well, remember my answer to "is it possible" is almost always "Yes." It would be possible, if I understand you correctly, to only have an error if there are more than 10 assignments other than X1, X2, etc. But we can come back to that if you want to pursue it.
I haven't had a chance to look closely at your other sample files, but you certainly have a lot of data consolidated there. You could "automate" various logic. For example, you could have a conditional format rule to highlight holidays. You could modify the data validation rule to prevent scheduling employees if they have leave scheduled, or if it's a holiday. You might want to change the data validation rule's behavior to allow you to schedule people anyway, but just warn you, so you don't end up trapped by the computer's logic when you just need to get things scheduled as you know they should be.
Chinese?
Asa
Asa,
Chinese.. :0... I couldn't figure out how to change it to English days of the week. You've helped with that without knowing it.
I like the idea of highlighting holidays. But I'll keep the error message. I can always add another assignment blow if i need to. If there were a way to move the unused assignments up as create the schedule, that would be cool. I'll look at that later.
Then, yes, I'd love to come back to that.
Here's my attempt to edit the formula to highlight every 14th day. I tried using your formula to highlight every 21st day. That didn't work. Maybe my "applies to" formula is flawed.
Assignment Schedule-3- Day14-attempt.xlsx
thanks
Mark
Chinese.. :0... I couldn't figure out how to change it to English days of the week. You've helped with that without knowing it.
I like the idea of highlighting holidays. But I'll keep the error message. I can always add another assignment blow if i need to. If there were a way to move the unused assignments up as create the schedule, that would be cool. I'll look at that later.
Then, yes, I'd love to come back to that.
Here's my attempt to edit the formula to highlight every 14th day. I tried using your formula to highlight every 21st day. That didn't work. Maybe my "applies to" formula is flawed.
Assignment Schedule-3- Day14-attempt.xlsx
thanks
Mark
Hi Mark,
Ah!! That explains it
I have my calendar in Microsoft Outlook set to use Chinese zodiak names... because you can...
Yes, there are various ways the unused assignments can shift up,
Ok, it looks like both your formula formula and your applies-to formula might need changes.
For applies-to, you have
Where XEA$2 is in the formula, that should be a reference to the day of the month for the current column. It should read C$2[/code].
I'll report back when I look more closely at your existing files.
Asa
Ah!! That explains it
I have my calendar in Microsoft Outlook set to use Chinese zodiak names... because you can...
Yes, there are various ways the unused assignments can shift up,
Ok, it looks like both your formula formula and your applies-to formula might need changes.
For applies-to, you have
Code:
=$C$2:$AG$2
That will only highlight cells on row 2 (where the day number is) but there is nothing wrong with it per se. To highlight the column, you could use [code]=$C$2:$AG$30. You could also use some format aspect besides background color, so that it wouldn't conflict with your other formats. Maybe most appropriately, if this is appealing, would be a Pattern (under Format, Fill tab, Pattern Color and Pattern Style.) That will sit on top of the background color in a pattern. If you want to retract the formatting you've already specified for a background fill, just hit the clear button at the bottom of the Format dialog before choosing new format options.
For the formula, your spreadsheet shows:
=MOD(DATE(2012,1,1)-DATE(YEAR($A$1),MONTH($A$1),XEA$2),14)=0Where XEA$2 is in the formula, that should be a reference to the day of the month for the current column. It should read C$2[/code].
I'll report back when I look more closely at your existing files.
Asa
Asa,
I had that problem yesterday while editing my built spreadsheets at work. After inputting the formula and entering ok. I didn't notice or think to check if the formula changed from what i put in. Now it works. I specifically adjusted the formula to highlight just the one box. Thanks.
Where would i find a formula to change all my Paid Leave assignments(PL1, PL2, PL3, ect...)to the color brown automatically? I also noticed i can't override my weekend color. Is there a way to do that?
I guess I'm looking for a conditional formula that says if a cell's value is (PL1,PL2,ect.) then (format "brown")
I'll work on it to see if i can figure it out.
Mark
Mark
I had that problem yesterday while editing my built spreadsheets at work. After inputting the formula and entering ok. I didn't notice or think to check if the formula changed from what i put in. Now it works.
I specifically adjusted the formula to highlight just the one box. Thanks.Where would i find a formula to change all my Paid Leave assignments(PL1, PL2, PL3, ect...)to the color brown automatically? I also noticed i can't override my weekend color. Is there a way to do that?
I guess I'm looking for a conditional formula that says if a cell's value is (PL1,PL2,ect.) then (format "brown")
I'll work on it to see if i can figure it out.
Mark
Mark
Thank you ASA,
=LEFT(C4,2)="PL"
I don't really understand what it means but i figured out how to fix it
=Left (C2,2)="PL"
EAS-revised11.18.2011.xlsx
I'm ready for this one.
Mark
=LEFT(C4,2)="PL"
I don't really understand what it means but i figured out how to fix it
=Left (C2,2)="PL"
EAS-revised11.18.2011.xlsx
I'm ready for this one.
Mark
Hi Mark,
Sorry I haven't been able to get back to you sooner. I'm still pretty busy..
Do you mean you don't understand why the change to C2 was in order? Well, in a conditional format rule, the formula must be written as it should be evaluated for the top-left cell in the Applies To range. Since we are interested in formatting the cell's color based on it's own value, the cell reference in the formula should BE the top left cell of the Applies to range... so it will be "looking at" itself. Then, since we used a relative reference (no dollar signs), it will look at "itself" for each and every cell in the Applies To range.
I wrote the formula assuming it would be applied to a range originating in C4 since that's the top left cell of the data entry area.
~~~~
OK! Next!
To restrict entry to 10 assignments per pay period (plus however many X assignments you like), we can extend the Data Validation rule like this....
Current rule:
Copy it across the row to every cell through AG31.
This formula assigns a number to each pay period, enabling the data validation rule to count how many assignments an employee has in any given pay period with relative ease.
The calculation is: # of days since 1/1/2012 divided by 14 (number of 2 week periods), rounded down to a whole number by way of the INT()[/code] function.
Feel free to label the row (good idea) and also to hide it (if of no particular interest of itself).
Next, actually enter/apply the data validation rule.
To do so, highlight the entire data entry area from C4 in the top left to AG18 in the bottom right. Then select Data on the ribbon, and click Data Validation in the Data Tools group.
Delete the existing formula and paste in the new one. While you're here, you may want to review the options in the Input Message and Error Alert tabs. Note that changing the style of the Error Alert message also changes Excel's behavior. A "Stop" alert is what you have now. You can "Retry" (choose something else to enter in the cell) or "Cancel" (undo what you just tried to enter and stop editing the cell). The other error alert styles are: Warning (user is given option to override the data validation rule) and Information (a less aggressive warning that doesn't have a button to go back and change your entry - just OK/Cancel).
More on data validation here: Apply data validation to cells.
Since our data validation formula has gotten so long and complex, I'd like to break it down:
[pre]=AND( // all these condition must be true...
COUNTIF(C$4:C$18,C4)=1, // 1. The assignment(C4) has been used once today.
NOT(ISERROR( // 2. This does not result in an error...
MATCH(C4,C$19:C$30,0) // The assignment(C4) is in the available assignments (C$19:C$30).
) ),
COUNTIFS( // 3. All these conditions are met simultaneously no more than 10 times...
$C$31:$AG$31,C$31, // The pay period($C$31:$AG$31) is the pay period for today(C$31).
$C4:$AG4,"<>", // The employee assignment($C4:$AG4) has not been left blank.
$C4:$AG4,"<>X*" // The employee assignment($C4:$AG4) does not begin with "X".
) <= 10
)[/code][/pre]
~~~~
The only limitation I see to this 10 assignment/period restriction being effective is that the formula can only see assignments for the current month. It can't enforce this rule for the first pay period of the month, unless that pay period happens to have begun on the 1st of the month.
Asa
Sorry I haven't been able to get back to you sooner. I'm still pretty busy..
Do you mean you don't understand why the change to C2 was in order? Well, in a conditional format rule, the formula must be written as it should be evaluated for the top-left cell in the Applies To range. Since we are interested in formatting the cell's color based on it's own value, the cell reference in the formula should BE the top left cell of the Applies to range... so it will be "looking at" itself. Then, since we used a relative reference (no dollar signs), it will look at "itself" for each and every cell in the Applies To range.
I wrote the formula assuming it would be applied to a range originating in C4 since that's the top left cell of the data entry area.
~~~~
OK! Next!
To restrict entry to 10 assignments per pay period (plus however many X assignments you like), we can extend the Data Validation rule like this....
Current rule:
Code:
=AND(COUNTIF(B$4:B$18,B4)=1,NOT(ISERROR(MATCH(B4,B$19:B$30,0))))
New rule:
[code]=AND(COUNTIF(C$4:C$18,C4)=1,NOT(ISERROR(MATCH(C4,C$19:C$30,0))),COUNTIFS($C$31:$AG$31,C$31,$C4:$AG4,"<>",$C4:$AG4,"<>X*")<=10)
The new rule depends on a new row of formulas added to the spreadsheet.
In row 31, just below the Available Assignments:
Enter this formula in C31: [code]=INT((DATE(YEAR($A$1),MONTH($A$1),C$2)-DATE(2012,1,1))/14)Copy it across the row to every cell through AG31.
This formula assigns a number to each pay period, enabling the data validation rule to count how many assignments an employee has in any given pay period with relative ease.
The calculation is: # of days since 1/1/2012 divided by 14 (number of 2 week periods), rounded down to a whole number by way of the INT()[/code] function.
Feel free to label the row (good idea) and also to hide it (if of no particular interest of itself).
Next, actually enter/apply the data validation rule.
To do so, highlight the entire data entry area from C4 in the top left to AG18 in the bottom right. Then select Data on the ribbon, and click Data Validation in the Data Tools group.
Delete the existing formula and paste in the new one. While you're here, you may want to review the options in the Input Message and Error Alert tabs. Note that changing the style of the Error Alert message also changes Excel's behavior. A "Stop" alert is what you have now. You can "Retry" (choose something else to enter in the cell) or "Cancel" (undo what you just tried to enter and stop editing the cell). The other error alert styles are: Warning (user is given option to override the data validation rule) and Information (a less aggressive warning that doesn't have a button to go back and change your entry - just OK/Cancel).
More on data validation here: Apply data validation to cells.
Since our data validation formula has gotten so long and complex, I'd like to break it down:
[pre]=AND( // all these condition must be true...
COUNTIF(C$4:C$18,C4)=1, // 1. The assignment(C4) has been used once today.
NOT(ISERROR( // 2. This does not result in an error...
MATCH(C4,C$19:C$30,0) // The assignment(C4) is in the available assignments (C$19:C$30).
) ),
COUNTIFS( // 3. All these conditions are met simultaneously no more than 10 times...
$C$31:$AG$31,C$31, // The pay period($C$31:$AG$31) is the pay period for today(C$31).
$C4:$AG4,"<>", // The employee assignment($C4:$AG4) has not been left blank.
$C4:$AG4,"<>X*" // The employee assignment($C4:$AG4) does not begin with "X".
) <= 10
)[/code][/pre]
~~~~
The only limitation I see to this 10 assignment/period restriction being effective is that the formula can only see assignments for the current month. It can't enforce this rule for the first pay period of the month, unless that pay period happens to have begun on the 1st of the month.
Asa