Solved

Duplicating relative conditional formatting over a large range of cells in Excel

Posted on 2010-11-23
27
299 Views
Last Modified: 2012-06-27
Hello,

In Excel (2007), how can a range of cells with Conditional Formatting (CF) be copy/pasted into another range of cells in a way that RELATIVE CF is transferred?

As an example, suppose you want to create a full-year calendar beginning with the current week as shown in Fig. A,
 Fig. Ain which dates are calculated as shown in Fig. B
 Fig. B(except Sundays which are calculate from the previous Saturday)

Also, suppose you want CF applied so that the current day will automatically change to a yellow fill.  This can be done by setting a separate cell (like A1) to

    =TODAY()

and then applying CF as shown in Fig. C
 Fig. Cwhich produces the result shown in Fig. D
 Fig. D
Now, the question is how can that same CF be applied to the other days in the calendar?  If the CF day (the 23rd) is simply copy/pasted to another day (the 24th), you end up with the situation shown in Fig. E
 Fig. Ewhere, because the original reference in the 23rd is absolute ($G$3), now the 24th lights up also.  Thus, although the relative formula for the date is OK (showing the value, 24), the CF for all the cells in the range comprising the 24th is actually tied to the 23rd.

Alternatively, if you go back and change the initial CF to a relative reference (Fig. F),
 Fig. Fthen it only formats the upper-left cell in the range (Fig. G).
 Fig. GTherefore, aside from doing it by hand, how can something the size of this calendar be CF, let alone, something many times larger?

Thanks
0
Comment
Question by:Steve_Brady
  • 10
  • 10
  • 7
27 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34201790
Try =Or(G$3=$A$1,H$3=$A$1)
Using G$3 makes the column relative but the row absolute
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34201795
Putting both G and H should make it work for the offset. (might need F and G, I didn't test yet).
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34201813
Okay, I tested it and it's working just fine. Alternatively you could select your whole sheet and use this formula
=and(row()>3,row()<12,OR(A$4=$A$1,B$4=$A$1))
0
 
LVL 80

Expert Comment

by:byundt
ID: 34201957
Steve,
One trick you may like:
1) Click anywhere in a cell reference in a formula by in the formula bar (such as between the D and the 1 in the formula =D1+G13*H1
2) Click the F4 key repeatedly to toggle between the four possible absolute and relative address modes like $D$1, D$1, $D1, D1, ...

If you select a character between two cell references (such as + or :) then the F4 toggles both the cell before and after through the four absolute and relative possibilities.

This same trick with the F4 also works in the Conditional Formatting dialog.

Brad
0
 

Author Comment

by:Steve_Brady
ID: 34202004
Thanks for the responses.

>>Try =Or(G$3=$A$1,H$3=$A$1)

Pasting this in CF for the 23rd (F3:G11) results in this:

 Fig. 1
In which only column F is yellow.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34202014
Use =Or(F$3=$A$1,G$3=$A$1)

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34202020
My big formula has an error. Apply this to the whole sheet
=and(row()>=3,row()<=11,OR(A$3=$A$1,B$3=$A$1))
0
 

Author Comment

by:Steve_Brady
ID: 34202293
>>My big formula has an error. Apply this to the whole sheet
=and(row()>=3,row()<=11,OR(A$3=$A$1,B$3=$A$1))

Tommy, you are getting closer!  It works great for any day this week but when I copy/pasted the week down to form next week, it no longer worked.  It would be nice to have a "template" to just paste at the bottom to form a new week but I don't know if that is possible.

I also tried, after making a third week, changing the bottom row term from,

    row()<=11,   to   row()<=29,

    i.e.  =and(row()>=3,row()<=29,OR(A$3=$A$1,B$3=$A$1))

to match the new bottom row, but with A1 = Nov 23, that gave me this:
 Fig. 3It was the same for A1 = to any day of this week (e.g. A1 = Nov 25 highlighted 11/25, 12/2 and 12/9).  However, setting A1 to any other date (11/28-12/11) produced no yellow anywhere.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34202306
Ah, you are going down rows. This will take a modulo operator, give me a minute.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34202338
Okay, use this condition for the whole sheet.
=OFFSET($A$1,9*FLOOR((ROW()-3)/9,1)+2,2*FLOOR(COLUMN()/2,1))=$A$1
0
 

Author Comment

by:Steve_Brady
ID: 34202401
A what-ulo operator?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34202411
I thought I was going to use modulo (the mod function). mod is a good way to make a lot of numbers transform into a few numbers as are integer division and rounding. I ended up using floor. Anyway, as long as you leave your calendar set up like it is this will work
=OFFSET($A$1,9*FLOOR((ROW()-3)/9,1)+2,2*FLOOR(COLUMN()/2,1))=$A$1
Note, the 9s are for the height of the day. If you make the days taller, make the 9s bigger
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34202422
The 2s in 2*floor(column()/2 are for the width of the days.
The only other thing to worry about is the shifting, this is a bit more complicated, but if you leave the number of blank rows above and to the left of the calendar as they are it should always work. If you do end up wanting help adjusting this again later, just click on 'ask a related question' and it will email me and I can help.
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

 
LVL 80

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 34202460
Modulo means divide by n and return the remainder. The MOD function does this. I selected B3:O48 and applied the following Conditional Formatting "Formula is" criteria:
=OFFSET(B3,-MOD(ROW()-3,9),MOD(COLUMN()+1,2))=$A$1
0
 
LVL 80

Expert Comment

by:byundt
ID: 34202482
Sample file attached using MOD function for Conditional Formatting
CalendarQ26635378.xlsx
0
 

Author Comment

by:Steve_Brady
ID: 34209160
>>One trick you may like:....Click the F4 key repeatedly to toggle between the four possible absolute and relative address modes

Thanks Brad.  That's one trick that I have known for a while and it is a big time saver.  However, don't let my knowing that one prevent you from posting others anytime you think of them because you have enhanced my Excel-ability many times in the past and I am looking forward to you doing the same many more times in the future!
0
 

Author Comment

by:Steve_Brady
ID: 34210471
Thanks form the responses.

>>=OFFSET(B3,-MOD(ROW()-3,9),MOD(COLUMN()+1,2))=$A$1

Brad, I downloaded your file and it obviously works but since I have several places to apply this type of Conditional Formatting (CF) function and not having used MOD before or OFFSET this way, I have spent some time trying to understand why/how it works and I'm not quite there:

1)  It appears that you can copy/paste a single or range of cells and preserve the CF as long as the copy range and paste range are both within the original range (OR = B3:O48) to which the CF was applied.  Is that correct?

2)  If some range within the OR is copied and then pasted outside of the OR, the CF no longer applies or at least is not correct.  If that is true, then it's clear that trying to determine the final range at the beginning of the project is important.  However, knowing that is not always possible, is there some way, or what is the best way to enlarge the OR after the CF has been applied?

3)  Inserting and/or deleting rows/columns once the CF is applied also messes up the OR -- particularly downstream (down and right) from the point of insertion.  If that is true...(same questions as above).

4)  Regarding the actual CF formula, can you get me straightened out on how it works?  Fig. 1 shows your CF formula and the file you posted with modifications to show only two weeks. The date in A1 has been set to Nov 10 and several unneeded rows in the first week have been hidden.  Also, the offset origin for your formula is green and the gray numbering in Row 2 and Column A are relative to that origin.
 Fig. 1
Fig. 2 shows an arbitrarily-selected cell (H17) inside Nov 10 with its numeric (absolute) coords.  There's also some other info to help me stay on track.
 Fig. 2
In Fig. 3, I changed the formula to an IF form (minus the equal sign) with the letter "Y" to indicate if it is true.  (Can I do that?  Probably not since where I'm going with this does not turn out well...)  I also substituted the ROW() and COLUMN() values as they would be in cell H17.
 Fig. 3
Finally, I simplified to get the results shown in Fig. 4.
 Fig. 4
Those last three IF formulas all return #REF! when equal signs are inserted so, as I said earlier, I know I did something wrong.  However, I think I understand the idea, namely, to get back to the date in the upper-right corner -- from any cell in the range representing a single day -- in order to see if it matches A1 (Fig. 5).
 Fig. 5
The main thing I don't understand is how you get the resulting OFFSET coords (-5, 1 in this case), to measure from H17 since your OFFSET formula specifies B3 as the origin.

Thanks



"A person who has never made a mistake is a person who has never tried anything new." ~Albert Einstein
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34210539
Just take one you can apply to the whole sheet. If you want it to ignore certain rows do something like this
=AND(OFFSET($A$1,9*FLOOR((ROW()-3)/9,1)+2,2*FLOOR(COLUMN()/2,1))=$A$1,ROW()>3,COLUMN()<60)

So it will only format rows > row 3 and columns < column 60
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 34210799
Steve,
In your posted problem, each day consisted of 9 rows by 2 columns, with the date in the top right corner. The top left corner of the first row was cell B3. The formula suggested was specific to that starting cell, but could be generalized using the references to cells B3, C3 and A1, 9 rows and 2 columns as follows:
=OFFSET(B3,-MOD(ROW()-ROW($B$3),9),MOD(COLUMN($C$3)-COLUMN(),2))=$A$1
In this formula, cell A1 is a cell containing TODAY() function, B3 is the top left cell of the calendar and conditional formatting selection, and C3 is the cell within the "day" that contains the date.

In the above formula, you can replace ROW($B$3) with the constant value it returns (3). Likewise, you can replace COLUMN($C$3) with the constant value it returns (3). Obviously, if your calendar has a top left cell other than B3, then the values returned by these expressions may vary.

If you select cell H17 and look at the Conditional Formatting formula in that cell, you will find that it has changed to:
=OFFSET(H17,-MOD(ROW()-ROW($B$3),9),MOD(COLUMN($C$3)-COLUMN()),2))=$A$1
This is because the first mention of B3 used relative addressing. You need a reference to cell I12, which OFFSET(H17,-5,1) nicely provides.


You can insert/delete rows and columns as long as they are above and to the left of the first cell in the calendar (B3) or below and to the right of the last cell (O48). The Conditional Formatting formula above will adapt automatically, as shown in the attached workbook.


By way of explanation, the formula uses B3 as the starting point assuming that the top left cell of the calendar is B3 and that it is also the top left cell of the selection when you create the Conditional Formatting. Having done this, you may then copy and paste the formatting to any other cell in that same calendar. The reference to cell B3 is relative, and will automatically update.

I subtract the result of MOD(ROW()... because I want to get a reference to the top row (of 9) for that particular day in the calendar. The MOD returns the number of rows the cell being formatted is below the top row. Likewise, I need to add 1 if the cell being formatted happens to be in the left side of that particular day. The result of MOD(COLUMN($C$3)-COLUMN(),2)) returns a 1 if the cell was in the left side of the "day" and a 0 if on the right.

The magic of the MOD function is that it makes each "day" on your calendar an independent 9 row by 2 column range. All the OFFSET formula has to do is to return a reference to the top right cell in that range, then compare it to cell A1.

You can change the number of rows and columns in each "day" by changing the 9 and 2 in the MOD function.

Brad
CalendarQ26635378.xlsx
0
 

Author Comment

by:Steve_Brady
ID: 34219419
Brad, thanks a bunch for the detailed explanation.  

Before commenting or asking other questions, I need to get this resolved because it may be at the heart of the issue I posted earlier.  

In your explanation, you said:

>>If you select cell H17 and look at the Conditional Formatting formula in that cell, you will find that it has changed to:
=OFFSET(H17,-MOD(ROW()-ROW($B$3),9),MOD(COLUMN($C$3)-COLUMN()),2))=$A$1


I am obviously doing something wrong because when I look at the CF formula for cell H17, I see this:

=OFFSET(B3,-MOD(ROW()-ROW($B$3),9),MOD(COLUMN()-COLUMN($C$3),2))=$A$1

This is from the file you just attached but B3 is the reference for mine as well (see above).

Maybe we are talking about/looking at different things.  Included below, are some live action shots of me checking the CF for cell H17.  (I would post the video also if EE allowed it.  However, you may be able to view it tomorrow on ESPN's weekend highlights!)   :)

To check the CF, I did the following:

1)  clicked cell H17
2)  clicked the CF icon on my QAT (Fig. 1) which brought up the
3)  Conditional Formatting Rules Manager (Fig. 2) in which I clicked "Edit Rule" which opened the
4)  Edit Formatting Rule box (Fig. 3) which shows the formula.

Unless it's too small, you can see that it references B3 as shown above rather than H17.  If you can tell me what may be causing that difference, we may have it solved.

Thanks

 Fig. 1 Fig. 2 Fig. 3
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 34219588
Steve,

Silly me! You are quite correct about the reference being to cell B3 when you open the Conditional Formatting dialog for cell H17 in Excel 2007 and 2010. In Excel 2003 and earlier, it does show a reference to cell H17, however.

Microsoft faces a tough issue here. Conditional Formatting was difficult to understand in Excel 2003 whenever you tried to use relative addressing. This was especially true in VBA, where success or failure often hinged on which cell was active when you set the conditional formatting formula. The only sure way to get it right was to select the cell prior to setting its conditional formatting--one of the very few exceptions to my preaching about never selecting or activating a worksheet or range in VBA code.

For Excel 2007 and 2010, Microsoft has chosen to display the conditional formatting formula for the top left cell in the CF range. You therefore need to understand exactly what range the CF applies to before trying to interpret the CF formula. I predict that even more people are going to be confused by this way of presenting the data than were previously confused.

The macro in the snippet will display the conditional formatting formula and range that it applies to. It does so for the active cell.

Suffice to say, what happens under the hood is as follows:
1) Excel stores both the CF formula for the top left cell and the range that it applies to
2) For any other cell, Excel translates the stored CF formula into one with relative addresses that are comparable to those of the top left cell. It's as though the CF formula were stored in R1C1 notation--but displayed only in the form appropriate for the top left cell. To make the translation, you need to know both the range the formula applies to (so you can get the top left cell) and the stored CF formula.

If you put the CF formula in cell B3, then copy it and paste it in cell H17--the reference to cell B3 will change automatically to cell H17. Excel does the same thing in CF, only it doesn't make the translation part very explicit.

Brad
Sub TestCF()

Dim CF As FormatCondition

Set CF = ActiveCell.FormatConditions(1)

MsgBox CF.Formula1 & vbLf & vbLf & "Applies to " & CF.AppliesTo.Address    'Excel 2007 and later

'MsgBox CF.Formula1        'Excel 2003 and earlier

End Sub

Open in new window

0
 

Author Comment

by:Steve_Brady
ID: 34220205
Brad,

If anyone is silly my friend, it's aint you!  I'm the one who feels silly because after all this discussion I'm feeling more confused than ever -- so I went back to something much more basic.  Unfortunately, that confused me even more!  lol

Please help me with this:

I started anew with a blank sheet and with =NOW() in A1 (formatted as mmm d).  I then decided to have just one cell represent each day.  I fomatted each cell in this grid to "d" (Fig.1) and made some copies (Fig. 2).
 Fig. 1 Fig. 2I then selected the 1st grid (C3:I8) and CF to =$A$1 (Fig. 3)
 Fig. 3However, with that, as soon as I clicked OK, it immediately changed the whole thing to yellow (Fig. 4) even with no values entered.  Why would it do that?
 Fig. 4
Next, I selected only the 1st cell (K3) in the 2nd grid and entered =$K$3=$A$1 as the CF formula (Fig. 5)
 Fig. 5and just for kicks, I entered 11/26 into that same cell (K3), but to my total surprise, it did not light up (Fig. 6)
 Fig. 6
I tried some other things in the remaining grids Brad, but I've got myself so confused that I'm going to stop here and hope that you or someone can rescue me.  It may just me be but this whole CF process seems quite non-intuitive considering the rules and conventions used with regular formulas and how you can copy/paste them.

Thanks
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 34220414
Steve,
Conditional formatting is looking for a value that it can interpret as TRUE or FALSE. To Excel worksheets, FALSE is 0 and TRUE is most any other number.

So in your first example, the CF formula =$A$1 returns a number like 40508.81281366 (the date/time serial number for 7:30 PM on 26 November 2010) which CF interprets as TRUE. So all the cells turn yellow.

In your second example, you are testing whether the date 26 November 2010 in cell K3 equals the value in A1. Since the date is stored as the serial number 40508 (with no value after the decimal point), cell K3 will equal the value in A1 only if the formula is refreshed at exactly midnight. In other words, the two cells are never exactly equal, so CF gets a value of FALSE--no highlighting.

You should be able to see the above logic using formulas in cells:
=IF(A1,TRUE,FALSE)                    always returns TRUE
=IF(K3=A1,TRUE,FALSE)             never returns TRUE

To see what the exact values of the cells are, you can use formulas like:
=TEXT(A1,"#.0000")
=TEXT(K3,"#.0000")

Brad

P.S.
I was extremely impressed by your recent Comment in another thread: http:/Q_26639057.html#a34214914
You captured the essence of Experts-Exchange and nicely explained it. I was going to participate in the thread as Zone Advisor, but your post did better than anything I could have dreamed up. Many thanks!

Brad
0
 

Author Comment

by:Steve_Brady
ID: 34220503
Brad, thanks for the the compliment.  It means a lot, particularly coming from you.


Also, thanks for backing out with me to the broader view re CF.  The understanding and perspective conveyed in your last post will help immensly as I drill back down!  

In the mean time however, so that I can move past this snag to which I have been tethered for several days (and if it won't take too much time), would you mind telling me, in specific terms, just how you would CF the two examples in this thread or point out where that's been done?  

I trust that the solution will become clear as I spend some time going back through the comments and working through some examples but in the interest of a project I would like to wrap up this weekend, having a "plug & play" solution would be very helpful.

Thanks again
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 34220701
Steve,
Speaking to your first example, my approach was:
1) Put the following formula in cell A1:
=TODAY()
2) Create the calendar by putting the starting date in cell C3 and the following formulas in cells D3 and C4:
=C3+1          'Formula for D3
=I3+1           'Formula for C4
3) Copy the D3 formula across and then down one row
4) Copy the C4:I4 formulas down through row 8
5) Select cells C3:I8
6) Format those cells with a Custom format like:
d
7) Open the Conditional Formatting menu item
8) Choose to create a new rule based on a formula:
=(C3=$A$1)

The parentheses aren't necessary in this formula, but may help you to understand it a little more clearly. The expression inside the parentheses is a Boolean expression, and will return a TRUE or FALSE value. It is TRUE if and only if the date in cells C3:I8 matches the one in A1.

The relative reference (to cell C3) should always be the appropriate one for the top left cell in the selection.

For the second block of cells (K3:Q8) I tried using the "Highlight cells based on value" using the "Equal to" criteria for step 8. In this case I was allowed to click on cell A1 to choose the value. This method also worked--but resulted in the exact same CF formula as the first method.

Brad

RelativeCF-Q26635378.xlsx
0
 

Author Comment

by:Steve_Brady
ID: 34220739
Thanks a bunch Brad.  That's just what I needed!
0
 

Author Closing Comment

by:Steve_Brady
ID: 34304528
multiple good answers
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

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

9 Experts available now in Live!

Get 1:1 Help Now