Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    if/or reference to another cell (2000)

    Columns A, B, C, D & E in my current worksheet contains cell references to another file. The formula in each cell in the current worksheet contains a "if/then" statement that says, for example, if cell A1 of the linked file = "",the cell A1 of the current worksheet = "", if not, then another (unrelated) result occurs.

    My question: I want to insert a formula in, say, cell F1 of the current worksheet that says if either A, B, C, D or E = "", then F1 = "", else, sum A1:E1. Every time I try and insert either an or formula or a variation thereof, I get a #VALUE return.

    Any help?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if/or reference to another cell (2000)

    Something like this:

    =IF(COUNT(A1:E1)<5,"",SUM(A1:E1))

    BTW: The SUM function ignores text entries, so
    =SUM(A1:E1) should suffice.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: if/or reference to another cell (2000)

    A formula like this should work:

    =IF(OR(A1="",B1="",C1="",D1="",E1=""),"",SUM(A1:E1 ))

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: if/or reference to another cell (2000)

    Yep, Hans, it sure does the trick. Just for my info, would a formula that utilizes "iszero(A1)" or "isblank(A1)" work?

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

    Re: if/or reference to another cell (2000)

    I've never heard of a (built-in) function ISZERO.

    I'm afraid that ISBLANK won't work - ISBLANK only returns TRUE if the cell is really empty. In this case, the cell contains a formula, so it isn't empty, even if the formula results in an empty string.

Posting Permissions

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