Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Using Logical cell references (XP)

    If this works:

    =IF((CELL("contents",H12)=""),"blank","NOT blank")

    and so does this (as part of a longer formula) to refer to the cell adjacent and to the right of the current cell:

    ADDRESS(CELL("row"),CELL("col")+1)

    then why can't I substitute H12 in the first formula to a logical value like this:

    =IF((CELL("contents",ADDRESS(CELL("row"),CELL("col ")+1)=""),"blank","NOT blank")

    what I get is a formula error... I'm hoping it's something simple that I haven't spotted.

    In case you are wondering I want to use conditional formatting and it is predicated on the adjacent, right cell being blank (or not)

    many thanks

    liz

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

    Re: Using Logical cell references (XP)

    You don't need such convoluted formulas (I think) for conditional formatting. If you specify Formula Is in the first dropdown list in the Conditional Formatting dialog, and use relative references in the formula, they will adapt themselves automatically.

    Example: select D312, then select Format | Conditional Formatting...
    Select Formula Is, then enter =E3>0 (take care not to use $E$3), and click OK, the condition will refer to the cell to the right of the cell to be formatted: in D4, it will refer to E4, etc.

    Or do I completely misinterpret what you want?

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Using Logical cell references (XP)

    ADDRESS returns the reference as a string, not a true cell reference. Try:

    =IF(ISBLANK(INDIRECT(ADDRESS(ROW(),COLUMN()+1)))," blank","NOT blank")
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Using Logical cell references (XP)

    After seeing Steve's reply, I see I missed that you plan to use conditional formatting. In that case, =ISBLANK() by itself should do what you want, since the formatting will be applied if the condition is TRUE. So if your conditional format is in cell A1, your CONDITIONAL FORMAT FORMULA would be for =ISBLANK(B1) (no $'s), and set the A1 formatting required when B1 is blank.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    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

    Re: Using Logical cell references (XP)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> It was Hans not Steve

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Using Logical cell references (XP)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> You guys have the same accent.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    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

    Re: Using Logical cell references (XP)

    I know we type very similar answers (and many times even simultaneously the same), but I doubt we have the save accent <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

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

    Re: Using Logical cell references (XP)

    You don't speak Double Dutch? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  9. #9
    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

    Re: Using Logical cell references (XP)

    I don't even speak Single Dutch.

    Steve

  10. #10
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Using Logical cell references (XP)

    Hans,

    Thnak you for this simple solution, however I can't seem to do a multiple condition test... probbaly me being stupid! I want to say for cell A1 where B2 is not blank and A1 is blank highlight A1, is this possible?

    liz

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

    Re: Using Logical cell references (XP)

    You can use the AND function to combine conditions. In your example, you would select Formula Is from the dropdown lisr, and enter
    =AND(ISBLANK(A1),NOT(ISBLANK(B2)))
    in the box next to it. Another formula with the same effect is
    =ISBLANK(A1)*NOT(ISBLANK(B2))

Posting Permissions

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