Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Conditional formatting issue

    Hi all,
    First post here so be nice! Anyway, I'll cut to the chase:

    I am working on a spreadsheet that shows a year snapshot of a range of monthly performance results from a number of different sheets.

    The targets are reducing through the year, for example, cells C3 to N3 reduce incrementally from 1.6 to 1.28. Then cells C4 to N4 pull the actual result from the relevant sheet.

    I'm using simple formatting to show if the result is over target to make the text red and if under target the text is green.

    I have tried making a rule for C4 referencing C3, then dragging that cell across to N4, but all that does is keep the reference as C3 and changing the "applies to" to =$C$4:$N$4.

    Is there a way to copy/paste or drag the cell so it will change both sides of the formatting? So C4 references C3, D4 refrences D3, etc..

    I have about 8 different performance indicators that I need to apply this to so to make 2 rules individually for 8x12 cells is going to be a nightmare!

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Pedsy

    Welcome to the Lounge!

    When you make the formatting rule, try removing the dollar $ signs to make the rule 'relative'.

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    Pedsy (2015-04-20)

  4. #3
    New Lounger
    Join Date
    Apr 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks zeddy.
    Not sure why I didn't try that. I would have for any other formula so it makes sense.
    The $ sign automatically appears in the "applies to" section but I could remove it from the "rule" section.
    Even though the rule for the whole row shows C3 as a reference, it is now working as intended, thanks!

  5. #4
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    One of the reasons I favor "R1C1 reference style" (its name under "Excel Options") is that a relative formula looks the same in every cell in which it occurs. This difference is particularly helpful in rules for conditional formatting, and would clarify the reference Pedsy mentions that shows as "C3". It's all a matter of individual taste, of course, so my only actual recommendation is to give it a try.

Posting Permissions

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