Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Copy Conditional format formualas (Excel XP)

    Attached is a spreadsheet I need an easier way of conditional formating. What I have done is conditionally formatted row 9 to shade cells when the condition is true. i want to apply this formatting to the remaining rows, but when I successfully copied the formula (using the format painter), it changed the cell it was using in the formula.
    The sheet is a Skin sheet for a golf league, where you get a stroke on a whole if this condition is met: The hole's difficulty (row 4 and in bold) is less than half your handicap (Column B/2). in the attached sample, E9 meets this criteria because the hole's difficulty is 1 (hardest hole) and the adjusted (halved) handicap is 4 (9/2, rounded down). This player gets a stroke on that hole and it is shaded to reflect this.
    When copying the formula, it changes the reference row of 4 to 5. I then have to manually change each cell's formula back to reference row 4.
    I'm looking for an easier way to do that so I don't have to essentially work on each cell.
    I hope I wasn't too wordy, but look at the attached sample to see if it helps.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Conditional format formualas (Excel XP)

    Select cell C9.
    Set the formula for conditional formatting to =C$4<$B9/2 and click OK.
    You can now use the format painter to copy the conditional formatting to C9:K46.

    $ in a cell reference means that the reference is absolute, i.e. Excel should not change it when copying the formula. In C$4 the column C is relative, it will be changed to D, E, ... as needed, but row 4 is absolute, i.e. fixed. In $B9 the column B is fixed while the row number is relative.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Copy Conditional format formualas (Excel XP)

    Thanks Hans, I forgot about the "$".

Posting Permissions

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