• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting of Date Changes on Different Workbooks

L

Lo Baan

New Member
Hi Chandoo,


I am very impressed with your website and all the experts in your forum. I am a novice at all the functions in Excel. I use Excel 2002 and create a monthly report of states that list 5 different dates for each state representing different stages of project implementation. Dates can change from month to month but the list of states are the same. Each month's report is in a different workbook.


I am struggling with trying to highlight the dates in the new month that changed from the last month. I tried comparing, EXACT, IF formulas, but it doesn't seem to allow me to do it between worksheets or workbooks. Our departments would like me to highlight (in color) the dates that changed. I gave this simple example below, but in reality, I have over 4000 dates which would take me quite some time to compare manually.


Example: August 2011

Alabama 2/02/11 3/27/11 5/16/11 7/24/11 8/10/11

Arizona 2/10/11 3/16/11 4/27/11 6/25/11 7/01/11

Colorado 3/04/11 3/28/11 5/02/11 8/12/11 9/25/11


September 2011

Alabama 2/02/11 4/10/11 5/16/11 7/24/11 8/10/11

Arizona 3/06/11 3/16/11 4/27/11 6/25/11 7/01/11

Colorado 3/04/11 3/28/11 6/19/11 8/12/11 9/25/11


The September report should highlight Alabama 4/10/11, Arizona 3/06/11, and Colorado 6/19/11.


Am I going about it in the wrong way? Any suggestions are greatly appreciated.


Thanks, Lo Baan
 
Hi Lo Baan ,


I am new to this forum ; so please let me know if I am wrong.


Conditional formatting does not work when the formulae include cells from different worksheets ; all the cells referenced in the conditional formatting formulae have to be in the same worksheet as the cells which are being formatted.


A work-around is as follows :


In your September 2011 worksheet , in a different area of the worksheet , have cells which have the formula giving the difference between the corresponding dates in the August 2011 worksheet and the September 2011 worksheet. For example , if the state names are in column A , and the first date for Arizona is in cell B5 , the date 3/06/11 will be in cell B5 of the September 2011 worksheet , while the date 2/10/11 will be in cell B5 of the August 2011 worksheet. In cell AB5 or some such different cell , type in the formula =B5-'August 2011'!B5


The above formula will give the difference between the two dates ; if this difference is 0 , then the two dates are identical , else they are different. Based on this , the cell B5 in the September 2011 worksheet can be conditionally formatted , since the formula will involve the cell AB5 in the same worksheet.


In your September 2011 worksheet , select the entire area which you wish to conditionally format , say B5 to F7 , in your example ; in the conditional formatting formula bar , type in =AB5<>0 , and select the format you wish , say underline and red colour ; click OK.


Try it out and let me know if it works.


Narayan
 
Hi Narayan,


Thanks so much for your work-around. I had to try it a few times and it works great. I have created the formula along with the conditional formatting formula and need to copy them to the next month's report. When time allows, I may my hand at creating a macro for this...


Thanks again,

Lo Baan
 
Hi Narayan & Lo Baan


On the issue if cf being based on cells in other worksheets, you can do this using range names. A bit like validation which only works for cell references in the same sheet unless you use a range name pointing to a range in another sheet.


In the cf when entering your formula refer to the range names using F3 or type in the range name.


John
 
Hi John ,


Thanks for the info. I didn't know this when I posted my answer , but came across it later in this forum.


Narayan
 
Narayan no problems, and I didn't want to seem to be correcting you as I have observed your very broad knowledge as you have answered a heap of wide ranging problems, so great stuff and keep it up. I only know about this little issue because I have a lot of situations where a dump of data from 3rd party software is required and this clobbers what was there before and I need to use a lot of conditional formatting. So I had to find a way to ensure cf criteria could be somewhere safe on another sheet.
 
Hi Narayan and John,


Great to learn more new techniques. My highlighting of different dates is working fine, however, because changes to dates are entered by other people. I noticed that dates are not only changing but are also being added or deleted. The conditional formatting or the formula to capture a date change is not able to capture whether there is a new date or a date that has been deleted.


Any suggestions for this? I noticed that the formula to capture the date difference is giving me "#VALUE!" when one of the dates to compare is not there.


Thanks, Lo Baan
 
Hi Baan ,


It depends on what your requirement is. Assuming that such missing dates are going to happen , what is your decision ? How do you want the date difference to be calculated ?


Assume the following conditions ; are they possible , and if yes , then what is to be done ?


1. A date is present in August 2011 , but not present in September 2011 i.e. a previous month has a date which a later month does not have.


2. A date is not present in August 2011 , but present in September 2011 i.e. a previous month does not have a date which a later month does.


Narayan
 
Hi Narayan,


Yes, both conditions are possible. In either case, I need to highlight in the September report that there was a change from the August report. Either there was a previous date or not.


Would this mean I need an additional coditional formula or an additional formula for comparing?


I appreciate the help...


Thanks, Lo Baan
 
Hi Lo Baan,


I have a feeling I could help, but I wonder if you could answer some questions:


- is the current month workbook saved using the same name as the prior month, or do they have differing names, like "[standard workbook name] August 2011.xls" and "[standard workbook name] September 2011.xls"?


- on the September workbook, do you need to list the dates that were deleted from the August workbook? (which I believe is Narayan's first example 1, date in August not in September)


- you say that you are comparing 4000 dates. But you have 50 states, with 5 different dates each. 50x5=250. Am I missing some detail that increases the total number of dates to 4000?


- does it matter to you whether the August workbook needs to be open while you are looking at the September results?


Thank you,

Daffy
 
Hi Daffy,


I appreciate any help. Here are my answers to your questions:


- Yes, each month is a different workbook with that month's date as part of the name.


- To answer your second and third question, I need to mention the the workbook is set up with static cells for each state, by product line, and by companies in that state, and by many project tasks.

Example using California:

State Product Company NB Date RB Date Input Proposal Final Date

CA Auto ABC 12/31/11 09/22/11 10/20/11 11/10/11 12/31/11

CA Home ABC 12/10/11 05/23/11 06/20/11 07/11/11 12/10/11

CA Boat XYZ 02/02/12 10/15/11 11/05/11 12/01/11 02/02/12

CA PELP WWW

CA Bike MMM 03/05/12 11/20/11 12/15/11 01/20/12 03/05/12

The workbook has many other project task dates but the static cells noted above stay the same from month to month. The dates are the only thing that can change or be eliminated from month to month. Also these dates are revised by different people in the department.

There are 50 states, each state has 5 product lines, each product line can have 4 companies, and then 5 project dates = 5000 dates.


- It doesn't matter whether the prior month's workbook is opened as long as the formula that Narayan suggested can determine a change in date.


While the formula is helping me to highlight changes in dates from prior month to present month, I'm not sure how to highlight when either a date has been inputted or removed to the present month. Current compare formula gives me #VALUE! whenever there is a blank in the prior or current month.


Hope this helps to give you a better idea of my monthly tasks.


Thanks, Lo Baan
 
Hi Baan ,


Can you see the worksheet , and verify whether it is what you want ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21113


Narayan
 
Hi Narayan,


Yes, I was able to see the worksheet. Everything that is highlighted is accurate to what I was looking for. When dates change, September dates show the dates highlighted. The only thing was when Alabama in column F had a date for August and then in September it was blank. I need to show the blank cell filled in yellow, again to show a date was removed.


Thanks for your help,

Lo Baan
 
Hi Baan ,


If you CF a cell using the formula :


=ISBLANK(cell_address)


with the colour YELLOW , then you should get what you want. Add this rule to the other rules for that cell.


Narayan
 
You must log in or register to reply here.
Back
Top