Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting Formulae (2003 SP2)

    Greetings.

    Are there limits to what formulae you can put into conditional formatting?

    I have a complicated spreadsheet which has several columns of numbers in paired columns. Each column of the pair has a percentage in the first column and the number on which the percentage is based in the second (e.g. 10% [of] 44). If the number in the left is outside the limits for that parameter, then I want to colour BOTH cells red.

    For readability, my limits for each parameter are in a column, but the paired columns go across the page. I therefore created a conditional formatting formula to calculate where to find the limit to look up so that I could easily copy the formulae across the page and not have to craft each one by hand. What I got was:

    =IF(F27<INDIRECT(ADDRESS(INT(COLUMN(G27)/2)+6,4),TRUE,FALSE)

    In other words, if the percentage in column F was less than the value in D9, then the condition was true and the colour of the number in column G was red. Except that it doesn't work. It evaluates perfectly if I copy the formula into the workbook somewhere, but in conditional formatting it will not work. On the other hand, if I replace COLUMN(G27) with 7, which is simply what it equates to, and leave the rest of the formula intact, then it works in the conditional format.

    What is going on?

    Thanks,
    Stuart

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

    Re: Conditional Formatting Formulae (2003 SP2)

    There's a closing parenthesis missing. The formula can be shortened to
    <code>
    =F27<INDIRECT(ADDRESS(INT(COLUMN(G27)/2)+6,4))
    </code>
    and this works for me in conditional formatting.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting Formulae (2003 SP2)

    Hans, thanks. That seems to work for me too, now.

    Cheers,

    Stuart

Posting Permissions

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