+ Reply to Thread
Results 1 to 17 of 17

Adapting Conditional Formatting

  1. #1
    Registered User
    Join Date
    08-11-2008
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013 64-bit
    Posts
    57

    Adapting Conditional Formatting

    Currently I'm using the top/bottom conditional rule formatting so that the highest figure is displayed in green and the lowest in red (as shown below). The issue is, as I have a set of data that is made up of 20 or 22 entries depending on what the user selects in the dropdown menu, there is an issue getting the conditional formatting to adapt to this. I can't seem to enter an IF statement for the top/bottom values option, so I'm unaware how I can achieve this. Only the first 20 rows of the data can be potentially highlighted but I want it so it adapts when there are 22 rows.

    http://oi43.tinypic.com/2wbzy8h.jpg

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Adapting Conditional Formatting

    A picture is no use to anyone - attach the Excel file instead.

    Pete

  3. #3
    Registered User
    Join Date
    08-11-2008
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013 64-bit
    Posts
    57

    Re: Adapting Conditional Formatting

    Quote Originally Posted by Pete_UK View Post
    A picture is no use to anyone - attach the Excel file instead.

    Pete
    Yes, here it is. Have a look: test2.xlsx.

    Change the drop down on the left from 1992/1993 to one of the more recent seasons to see the adaption from 22 to 20 rows.
    Last edited by ACM2; 06-12-2013 at 06:56 AM.

  4. #4
    Registered User
    Join Date
    11-29-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Adapting Conditional Formatting

    Hi

    As far as I can tell what you'd like to be able to do is conditionally format a dynamic range of cells, and I don't think that can easily be done (I'm not an expert, but it looks like that's the case from this: http://chandoo.org/forums/topic/dyna...onal-formating)

    This is not very neat (and looking at your spreadsheet you may well have thought of something like this already), but how about:
    1. define a dynamic named range for each of the various columns you want to highlight, e.g.:
    total_home as ='Results Grid'!$DM$4:OFFSET('Results Grid'!$DM$4,COUNT('Results Grid'!$A$4:$A$25)-1,0) (you can use OFFSET to specify the first cell in the range as well if need be - I can see some ranges change column as well as no. of rows depending on the season)

    2. apply a conditional format to the range DM4:DM25 using 'cells that contain', then cell value equal to =MAX(total_home) or =MIN(total_home).
    As cells DM24 and DM25 won't ever have the same as the max or min values in your named range (when you have 20 teams), I think this is a workaround?
    Last edited by Paul Stockdale; 06-12-2013 at 09:52 AM.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Adapting Conditional Formatting

    Maybe you should use a formula instead of the top/bottom 3. Highlight the cells from DM4:DR25, then click on Conditional Formatting | New Rule | Use a formula, then have a formula like this:

    =AND(ROW(DM4)<=MAX($A:$A),RANK(DM4,DM$4:DM$25,1)<=3)

    for the top 3. For the bottom 3 omit the ,1 from the end of the Rank function.

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    11-29-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Adapting Conditional Formatting

    Hi Pete - although this only applies the format to cells that are in the range, is it not true that the rank formula will return a first position for the cell with total points when that total is in the range DM4:DM25?

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Adapting Conditional Formatting

    Actually, I just realised that because the cells start on row 4 the formula should be:

    =AND(ROW(DM4)-3<=MAX($A:$A),RANK(DM4,DM$4:DM$25,1)<=3)

    By highlighting all the cells first and by using a mixture of relative and absolute addressing, Excel will automatically adjust the cell references (down and across) when you come out of the dialogue box.

    This was just a first stab as you had a lot of different CFs applied to that range, and there was also a lot of errors showing up. Maybe you need to add another condition to the AND, like DM4<>"".

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    11-29-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Adapting Conditional Formatting

    What I was meaning is that using a range from row $4 to row $25 for the RANK formula, in the case where there are only 20 entries in the data, you will pick up the total cell in the data you are ranking, even though that cell won't itself be formatted because of the AND formula.

    So if you use
    =AND(ROW(DM4)-3<=MAX($A:$A),RANK(DM4,DM$4:DM$25,0)<=3), you only get two cells formatted when there are 20 points in the dataset and you get 3 cells formatted when there are 22 points in the dataset.

    That's why I think you need to use some sort of dynamic range, ACM2.

    Apologies if I've misunderstood.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Adapting Conditional Formatting

    An interesting video on Conditional formatting that might be of interest to you can be found here:

    http://www.youtube.com/watch?v=skC-iNjegZ8
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Adapting Conditional Formatting

    @Paul:

    I see what you mean now. I'd leave the TOTAL on line 26 and have 2 blank rows when there are only 20 teams, which is what I thought the OP had done (must pay more attention !!) That would simplify some of the other formulae above. I suppose the range within the RANK formula could be made dynamic using INDEX in order to avoid that, but as the OP hasn't got back to us I'll wait for his/her response before doing anything else with it.

    Pete

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Adapting Conditional Formatting

    Actually, thinking about this further, you could avoid having a dynamic range by changing the <=3 term for the top-3 colour to:

    <=IF(MAX($A:$A)<22,4,3)

    as the top 1 will always be the total. This does not affect the formula for the bottom-3 colour.

    Hope this helps.

    Pete

  12. #12
    Registered User
    Join Date
    08-11-2008
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013 64-bit
    Posts
    57

    Re: Adapting Conditional Formatting

    Thanks for your responses guys. I'm a little confused though. I've tried what's been said but can only get it partially working. It adapts between 20 and 22 but it doesn't correctly show the top/bottom values unfortunately.

    Perhaps someone could edit my workbook and reupload it with it working (at least largely working!).

    Thanks.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Adapting Conditional Formatting

    I've applied the rules described above to the attached workbook - perhaps you can check it out more thoroughly than I can.

    Hope this helps.

    Pete
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    08-11-2008
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013 64-bit
    Posts
    57

    Re: Adapting Conditional Formatting

    Quote Originally Posted by Pete_UK View Post
    I've applied the rules described above to the attached workbook - perhaps you can check it out more thoroughly than I can.

    Hope this helps.

    Pete
    Okay, thanks a lot. I appear to have got it working fully by adjusting the formula slightly. Still a bit confused about what the actual formula is doing though, although I'm trying to figure it out.

    =AND(ROW(DM4)-3<=MAX($A:$A),RANK(DM4,DM$4:DM$25,0)<=IF(MAX($A:$A)<22,4,3))

    =AND(ROW(DM4)-3<=MAX($A:$A),RANK(DM4,DM$4:DM$25,1)<3)
    Last edited by ACM2; 06-13-2013 at 06:32 AM.

  15. #15
    Registered User
    Join Date
    08-11-2008
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013 64-bit
    Posts
    57

    Re: Adapting Conditional Formatting

    Quote Originally Posted by newdoverman View Post
    An interesting video on Conditional formatting that might be of interest to you can be found here:

    http://www.youtube.com/watch?v=skC-iNjegZ8
    Just tried that method and it seems to work best. Doing it with formulas appeared to create additional problems so using IF statements and a TRUE/FALSE table appears to be the more favourable solution. Thanks for the different methods though.

  16. #16
    Registered User
    Join Date
    04-12-2012
    Location
    Kariong, NSW
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Adapting Conditional Formatting

    Not sure if this helps but, I've used visual basic to determine cell color in this spreadsheet and you may find it useable or adaptable.

    Steve
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    08-11-2008
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013 64-bit
    Posts
    57

    Re: Adapting Conditional Formatting

    Anybody know the issue here? Look at cell DV24. It's highlighted green despite this seemingly going against the conditional formatting formula. Check it out and you'll see what I mean. ef429_CF_league_tables.xlsx

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1