Results 1 to 4 of 4
  1. #1
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts

    Trying to Understand

    I have searched far and wide for a suitable explaination without much success so hoping I can find the answer here.
    With conditional formatting assuming my work area is B11 to R41, when creating a formula I would write $B11=2 then in the box to cover where the formula is valid it would be =$B$11:$R$41

    What I would like to know is if I enter a "2" in for example B33 then the formatting works for that row, which part of the formula is doing this? How does B33 relate to just that row for example and why is it not defined in the formula box ( it just refers to =$B11=2?

    Thanks

    Alan

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Allen,

    The behavior you see is cause by the use of the MIXED reference in your formula, e.g. $B11.
    The B part is FIXED by the $ however the 11 (row) part is not so it will change to operate for each row.

    Per your parameters: =$B11=2
    cf1.JPG
    Notice how it operates for row 12 with a value of 2 in B12.

    If you change the formula to =$B$11=2
    cf2.JPG
    Notice how now since the reference is fixed on both parts (or an Absolute Reference in Excel speak) nothing is formatted because $b$11 is NOT equal to 2. However if $B$11 is set to 2 all cells will be formatted.
    cf3.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    AlanWade (2014-05-24)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    To expand on RG's comments. It is about Absolute vs relative references in cells. One way to look at it would be to imagine that you would copy the formula:
    =$B11=2

    into the range
    =$B$11:$R$41

    and imagine what the formulas would be [ignoring that they would be circular references]

    Since the Column B is Absolute [=preceded by a dollar sign($B)] but the row is relative [not preceded by a dollar sign] in the cells in row 11 B11:R11] the formula would amount to:
    =B11=2

    So for any cell in row11, if B would be 2 they would all be "TRUE" and meet the condition.

    That is the same for each row. Row 40 for example in Cells B40:R40 would all have:
    =B40=2

    So again they are all comparing to the value in col B for that row.

    Each cell can have 1 of 4 abs/rel combinations [Tip: when entering formulas the <F4> key toggles through the 4 possibilities:]
    =B11
    =$B$11
    =B$11
    =$B11
    If you had used B11 and copied the [=B11=2] into the B11:R41 cells, the cond format would be true when a particular cell had the value of 2
    If you had used [=$B$11=2] into the B11:R41 cells, the cond format would be true for all the cells when B11 equals 2
    If you had used [=B$11=2] into the B11:R41 cells, the cond format would be true for any cells in the column when the value in row 11 equals 2
    And to reiterate your example, when you had used [=$B11=2] into the B11:R41 cells, the cond format would be true for any cells in the row when the value in Col B equals 2

    Hope this helps,
    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    AlanWade (2014-05-24)

  6. #4
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts
    Thankyou both very much, that is easier to understand than the references I found on the net.

    Alan

Posting Permissions

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