Solved

Excel 2007/2010 Conditonal Formating function

Posted on 2012-04-07
49
340 Views
Last Modified: 2012-04-24
Hello again to all excel gurus,

As promised, there is a part two for another 500 points.  

I plan to build a calendar similar to the attached worksheet and want to be able to use conditional formatting that will use a few different conditions. Where I need the help most is in condition3.

Cond1: Highligh Date if First Shift occurs
Cond2: Highligh Date if Second Shift occurs
Cond3: Highlight From Date where duplicate Shift occurs . (How to setup Conditon where it will turn red when a dupshift occurs, and will let other days color update as well?)
ExcelFunctionHelp.xlsx
0
Comment
Question by:BajanPaul
  • 29
  • 20
49 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37820689
Try this file. I have deleted the previous month and next month dates from the calendar.
Copy-of-ExcelFunctionHelp.xlsx
0
 

Author Comment

by:BajanPaul
ID: 37821761
Its pretty close. I needed it broken out by unique work center.  Please refer to the setup I have attached.

Your help thus far is much appreciated.
ExcelConditionFormatHelp.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37823679
Although possible, this is not very straightforward. Before I attempt any further I would like to know exactly how your calendars would be laid out because any change to the layout may cause the CF to collapse. I might even suggest change to your layout.
0
 

Author Comment

by:BajanPaul
ID: 37840934
Please find attached calendar layout and data.  I have input my notes within the calendar to try and help explain what I am trying to accomplish.

I do not know if what I am after is possible, I am hoping it is.  
Essentially, I am trying to build a shift calendar to identify when shift 1 is taking off, Shift two is taking off and if there are times where both shift 1 and 2 are off at the same time.

Please help as I do not know how to go any further and what is the easiest way to do this.
Calendar-Test-4-11-12.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37846495
0
 

Author Comment

by:BajanPaul
ID: 37850969
Can you explain what you did in the attached file.  I do not see the additional changes.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37852051
I have applied conditional formatting to the dates.
0
 

Author Comment

by:BajanPaul
ID: 37852227
None of the dates are highlighted.  Did you get a chance to read over my notes with regard to a function that will pull in a 1,2,3?  If such a function can be written to identify 1|2|3 based upon unique work center and dates, then the condition formatting could be applied.  Just for clarification, I do not see where you applied any new conditions.  Please see attached screen shot.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37852410
What version of excel are you using? Did you try it in 2010?
0
 

Author Comment

by:BajanPaul
ID: 37852951
I have 2010 at the house.  I am using 2007 at the office.  Will check my 2010 when I get home in a few hours.  Can you also post in 2007 if possible.  Were you able to write a function that pulls in the 1,2,3 under the Calendar and then apply the conditional format?

Thanks
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37853015
No I did a formula which checks the values from the other sheet.
0
 

Author Comment

by:BajanPaul
ID: 37853105
I want to be able to create another 22 mini calendars within the first tab. Can I apply/ modify the function and conditional formula by unique work center calendar and it update each calendar with the appropriate colors when it sees a 1|2|3?  I want to be able to establish by month, when someone is taking off from first shift/second shift and if a condition exists where 1st/2nd shift are taking off on the same day.
0
 

Author Comment

by:BajanPaul
ID: 37853713
It is very close, and I do not know if there is there a way to account for in the condition for mid dates and also segregate the conditions by unique work center for each individual calendar?  For example:

Pri.WorkCenter     FromDate     ToDate    Shift     ZeroCap  
40441                    4/4/12          4/05/12    1
40441                    4/4/12          4/06/12    2            3

You were able to highlight the following:

4/4/12 was highlighted red ( This is Correct)
4/6/2012 was highlighted orange (You highlighted it and this is correct, minor change to color as it is supposed to be blue for shift two)
4/5/12 was not highlighted at all as it is supposed to be highlighted Red because 4/5/2012 shift 1 falls on the same day off as shift two. Is this even possible to accomplish?

As per my earlier response, I also need the conditions specifically broken out by work center as I want to create an additional 22 calendars.  

I just realized the condition could also exists where:

Pri.WorkCenter     FromDate     ToDate    Shift     ZeroCap  
40441                    4/4/12          4/04/12    1
40441                    4/4/12          4/06/12    2            3

the FromDate and ToDate for first shift or second shift could equal just one day, in the example above, i changed the FromDate and ToDate for the first shift.  So the conditional formatting will be:

4/4/12   4/4/12 ist shift (Red color)
4/4/12   4/6/12 2nd shift (Blue color for 4/5/2012 and Blue color for 4/6/2012)

I hope this is making sense.

As I stated above in earlier post, I want to establish the correct color for days off when 1 is present, 2 is present and 1/2 are present.  I guess this is a bit tricky because there are cases when shift one and two can start on the same day and not end at the same time.

Please don't give up on me as I believe you are getting very close.
0
 

Author Comment

by:BajanPaul
ID: 37853964
Please review the post before this one.

Please see example of what calendar is supposed to look like after Conditional Format is applied.  Please note, the conditions will be unique by work center.  What you have so far is extremely close.
ConditionalFormatCalendar.jpg
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37853971
I just woke up and was going through your detailed comment when I saw your next post (with the jpg) coming in. Before I start to digest what you want, can you tell me whether you can see the conditional formatting now? Why did you not see it previously? Is it a version difference?
0
 

Author Comment

by:BajanPaul
ID: 37855484
Yes.  I am back at the office and workign in excel 2007.  Funny enough I could not see the conditional formatting you applied in 2007.  I have 2010 on my computer at home and could see the conditions clearly and the calendar dates highlighted.  I will be in the office for the next 9hours.  If possibe, when you save and upload the next excel sheet, can you save it to earlier format so i can view it.

Your work and endureance on this project is very much appreciated.  

Paul
0
 

Author Comment

by:BajanPaul
ID: 37855575
Hey ssaqibh
I am doing researc online and and seeing there may be a problem with using conditional formattin and referenceing another worksheet in excel 2007.  Can it be done?
0
 

Author Comment

by:BajanPaul
ID: 37855942
What I just figured out in 2007 and conditional formatting is that for it to work, you have to name the range.  for instance, the holiday section of shiftdata.  I named the range:
=MATCH(D18,CamHoliday,0)>0                    intead of
=MATCH(D18,ShiftData!$U$4:$U$264,0)>0
and it worked just fine. I am going to assume this is one of the enhancements they made in 2010.
0
 

Author Comment

by:BajanPaul
ID: 37856517
I am having issues getting the other conditions (3 countifs to work).  I will wait for your next post.

Thanks again.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37857491
Can you upload your file with the range names?
0
 

Author Comment

by:BajanPaul
ID: 37858095
Hey ssaqibh

I just had my IT coordinator upgrade my work computer to use office 2010.  I just downloaded the Copy-of-Calendar-Test-4-11-12.xlsx (post: ID: 37846495).
We will not need to work in 2007 anymore as I can view the conditional formatting you applied.

-------------------------------------------------------------------

Is there something you want me to update now as we can work in 2010.

Paul
0
 

Author Comment

by:BajanPaul
ID: 37858644
I added another function question you may be interested in.
Ms Excel 2007/2010 Lookup / Match Funciton.

Please help on this one as well if time permits.

Paul
0
 

Author Comment

by:BajanPaul
ID: 37860449
Hey ssaqibh
You haven't give up on me? Is there anymore clarification you need from me?

Paul
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37860508
Please update me. As far as I have understood the conditional format is working now.

Is it working correctly?

What is the issue outstanding?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:BajanPaul
ID: 37861537
Hey ssaqibh,

I recently had my computer at the office upgraded to use Excel 2010. I can now see your conditions but they are not exactly what I am needing to complete the calendar (please see screen shot).

I need the conditions to do the following by individual work center calendar:

1) Highlight the holiday dates orange.
(This is correct, match formula works and I can implement this condition across all mini calendars that I create.)

2) Highlight Shift1 days off with Green

3) Highlight Shift2 days off with Blue

4) Highlight Shift1/Shift2 that are taking off the same day with Red.

5) This is the tricky part for example:

Pri.WorkCenter     FromDate     ToDate    Shift     ZeroCap  
40441                    4/4/12          4/05/12    1
40441                    4/4/12          4/06/12    2            3

4/5/12 was not highlighted with your condition. It is supposed to be highlighted Red because 4/5/2012 shift 1 falls on the same day off as shift two. I need to show all days off where there is a possible overlap.

Is this even possible to accomplish?

I hope I am making sense, please take a look at the picture again to see what the calendar is supposed to look like after conditional formatting is applied.

I hope this helps.
ConditionFormatCalendar.JPG
0
 

Author Comment

by:BajanPaul
ID: 37861694
Please see another image.  Please also read the post before this one.  I hope this helps.
CalImage2.png
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37862963
I am having problem understanding the criterias so there may be a number of clarifications to follow. Here is what I have understood although it is different from your image. Please follow this up.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37862966
0
 

Author Comment

by:BajanPaul
ID: 37863111
Pretty Close.  

Can you also look at the situation where the FromDate and ToDate are the same.:

Pri.WorkCenter     FromDate     ToDate    Shift     ZeroCap  
40441                    4/4/12          4/04/12    1
40441                    4/4/12          4/06/12    2            3

I tried to change Shift1 ToDate to 4/04/12 and i though it might have highlighted 4/5/2012 Shift2 To Blue.  It stayed Red.

Please let me know.

Paul
0
 

Author Comment

by:BajanPaul
ID: 37864017
Dear ssaqibh

It is extremely close.  Please see attached screen shot and excel with latest dates.  I hope this explains the final requirements.

Please let me know if you need further clarification.  In the event you get it to work, can you
play with the dates to see if they highlight under the various From and To date conditions.

Thanks very much for all your hard work, dedication and most of all patience.  I'd like to buy you a beer after is over, as I need one myself  :).



Paul
CalLastChanges.jpg
Calendar-Test-5-.xlsx
0
 

Author Comment

by:BajanPaul
ID: 37868395
How is everything going?
0
 

Author Comment

by:BajanPaul
ID: 37874439
I hope I have not scared you away.  Is what I am asking in post ( Posted on 2012-04-18 at 19:55:46ID: 37864017) even possible with conditional formatting?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37875299
Sorry for the delay. This thing requires some time and some tranquility before I can convince myself to sit down to attempt it. Try changing the formula for red to

=COUNTIFS(ShiftData!$C$1:$C$1111,INDIRECT(ADDRESS(15,INT((COLUMN(D18)-4)/8)*8+4)),ShiftData!$G$1:$G$1111,"<="&D18,ShiftData!$H$1:$H$1111,">="&D18)>1
0
 

Author Comment

by:BajanPaul
ID: 37875509
That worked perfectly!!  Is there a way to un-highlight within the existing conditions or maybe with a new condition, dates that extend from one week to another.  For example f if I put in the range:

From Date      To Date
4/4/2012              4/9/2012

4/4=Wednesday
4/5=Thursday
4/6=Friday
4/7=Saturday
4/8=Sunday
4/9=Monday
I promise this is the last question to make the calendar perfect.

Please see attached.
DontShowWeekend.jpg
Calendar-Test-6-.xlsx
0
 

Author Comment

by:BajanPaul
ID: 37876556
Dear ssaqibh,

I seem to also have another small issue.  I added 4 more mini-calendars within the workbook, applied the conditional formatting to all of them and the countif's formulas don't seem to be updating all the individual calendar dates correctly....

Could you please also tell me what I am doing wrong as I have applied the month and match formulas and they are updating all six calendars fine.  

My goal is to create a total of 22 mini calendars with the hope I can see all the days by shifts taking off by work center.

I have attached the excel with the 6 calendars in it.

When time permits, can you also take a look at the post above this one as I would also like to know if a condition can be created to un-highlight dates that extend over the weekend or if it is possible to write it in the existing conditions.

Respectfully,

Paul
Calendar-Test-7.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37876789
The last three conditional formatting formulas contain the term

INDIRECT(ADDRESS(15,INT((COLUMN(D18)-4)/8)*8+4))

which restricts the row for picking up the store name. Currently this is fixed at 15 and needs to be modified for any change.

For the given change replace the 15 in all three formulas to

INT((ROW()-15)/11)*11+15

This formula will continue to work if the next store numbers are found in rows 37, 48, 59....



For the previous modification....please give me more time.
0
 

Author Comment

by:BajanPaul
ID: 37876945
Modified First Countif:
=COUNTIFS(ShiftData!$C$1:$C$1111,INDIRECT(ADDRESS(INT((ROW()-15)/11)*11+15,INT((COLUMN(D18)-4)/8)*8+4)),ShiftData!$G$1:$G$1111,"<="&D18,ShiftData!$H$1:$H$1111,">="&D18)>1

Modified Second Countif:
=COUNTIFS(ShiftData!$C1:$C1111,INDIRECT(ADDRESS(INT((ROW()-15)/11)*11+15,INT((COLUMN(D18)-4)/8)*8+4)),ShiftData!$G1:$G1111,"<="&D18,ShiftData!$H1:$H1111,">="&D18,ShiftData!$I1:$I1111,2)

Modified Third Countif:
=COUNTIFS(ShiftData!$C1:$C1111,INDIRECT(ADDRESS(INT((ROW()-15)/11)*11+15,INT((COLUMN(D18)-4)/8)*8+4)),ShiftData!$G1:$G1111,"<="&D18,ShiftData!$H1:$H1111,">="&D18,ShiftData!$I1:$I1111,1)

If I understand you correctly, I needed to replace the 15 with the following:
INT((ROW()-15)/11)*11+15

I still could not get it to work correctly and don't know what I did wrong.  Please advise when time permits.  

Thanks,

Paul
Calendar-Test-8.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37876997
There was a mistake on the formulas. The ranges referring to Shiftdata should all have $ sign with both row and column referrences. Formula for red is ok as it has $C$1:$C$1111 etc. Change all such references of $C1:$C1111 to $C$1:$C$1111
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37878402
If you only need to unhighlight the weekends then you should consider excluding the weekends from the conditional formatting range.
0
 

Author Comment

by:BajanPaul
ID: 37879144
I am not following you about excluding the weekends from the conditional format range.  Can you give me an example please.

Paul
0
 

Author Comment

by:BajanPaul
ID: 37879170
Per your post (ID: 37876997), I changed all the row and column references and still cannot get all the calendars dates highlighted correctly.  Can you please take a look and if you have time, can you please give me an example on how to exclude weekends from conditional formatting?

Please see attached CAL7 with the adjusted formulas as you suggested.

Thanks

Paul
Calendar-Test-7.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37879322
I changed all the row and column references
You changed

$C1:$C1111
to
$C1:$C$1111
you should change it to
$C$1:$C$1111
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37879331
...weekends...

go to conditional formatting manage rules
for each of the format click on the right end of the "Applies to box" and then redefine the ranges to exclude the weekends.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37879333
Alternatively you can select the weekend dates and then
Conditional formatting
Clear rules
Clear rules from selected cells
0
 

Author Comment

by:BajanPaul
ID: 37882204
Hi ssaqibh
Please see attached screen shot and caltest9.  I was able to get the top three calendars working, however a few of the dates that should be in the bottom calendars are showing up.  I could not get the three bottom calendars to work.

With respect to the clear rules, this works great thanks for the suggestions!!

One other clarification I have on the formulas Shiftdata: in the red formula both G and H (no I) are fixed, should I do the same for the other formulas (G,H & I) ?

For example in red formula: $G$1:$G$1111 , H$1:$H$1111
                     in Blue formula: $G1:$G1111, $H1:$H1111, $I1:$I1111
                     in Green Formula: $G1:$G1111, $H1:$H1111, $I1:$I1111

Highlight Red (ZeroCapShift)
=COUNTIFS(ShiftData!$C$1:$C$1111,INDIRECT(ADDRESS(INT((ROW()-15)/11)*11+15,INT((COLUMN(D18)-4)/8)*8+4)),ShiftData!$G$1:$G$1111,"<="&D18,ShiftData!$H$1:$H$1111,">="&D18)>1

Highlight Blue (NightShift)
=COUNTIFS(ShiftData!$C$1:$C$1111,INDIRECT(ADDRESS(INT((ROW()-15)/11)*11+15,INT((COLUMN(D18)-4)/8)*8+4)),ShiftData!$G1:$G1111,"<="&D18,ShiftData!$H1:$H1111,">="&D18,ShiftData!$I1:$I1111,2)

Highlight Green (firstShift)
=COUNTIFS(ShiftData!$C$1:$C$1111,INDIRECT(ADDRESS(INT((ROW()-15)/11)*11+15,INT((COLUMN(D18)-4)/8)*8+4)),ShiftData!$G1:$G1111,"<="&D18,ShiftData!$H1:$H1111,">="&D18,ShiftData!$I1:$I1111,1)
CalTest9Image.png
Calendar-Test-9.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37882439
G H I should all be fixed in all formulas
0
 

Author Comment

by:BajanPaul
ID: 37886088
Got it!!  One last question before I close out this post, is there a reason for setting the shiftdata range to the following:

For example in red formula: $G$1:$G$1111 , H$1:$H$1111
                     in Blue formula: $G1:$G1111, $H1:$H1111, $I1:$I1111
                     in Green Formula: $G1:$G1111, $H1:$H1111, $I1:$I1111

Can I update the End Range to $5000 in all cases and it still work?

Thanks

Paul
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 37886161
It is your baby. You can make it 50000. The easiest is to choose the entire column $C:$C but then it makes the file very slow. I chose 1111 because I am too lazy to type 1000.
0
 

Author Closing Comment

by:BajanPaul
ID: 37887100
Dear ssaqibh
I wanted to say thank you for your help, dedication and most of all patience seeing me through this challenging exercise.  I look forward to working with you again in the future.

Respectfully,

Paul
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now