Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: ISBLANK

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to create a formula in a new cell that says if any of three other cells are not blank, then fill the contents of new cell with same text as in a different cell, otherwise don't put anything in the new cell.

    I can't write the correct formula. What's more, I cannot figure out how to cycle through more than one set of the formulae in each cell. This is important because I don't want to leave a blank cell (and therefore in this instance, a blank row) for every instance when the argument is true (all are blank).

    Any help is really appreciated. My skills are just not this developed, although I can conceive of what I want to do!

    Sharon

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Instead of ISBLANK, try using COUNT or COUNTA. Maybe something like =IF(COUNTA(ThreeCells)>0,CellAddress,"")

    [ This Message was edited by: lenze on 2002-03-29 07:56 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-03-29 07:49, SharonDrumm wrote:
    I am trying to create a formula in a new cell that says if any of three other cells are not blank, then fill the contents of new cell with same text as in a different cell, otherwise don't put anything in the new cell.

    I can't write the correct formula. What's more, I cannot figure out how to cycle through more than one set of the formulae in each cell. This is important because I don't want to leave a blank cell (and therefore in this instance, a blank row) for every instance when the argument is true (all are blank).

    Any help is really appreciated. My skills are just not this developed, although I can conceive of what I want to do!

    Sharon
    Lets say that A1, B1, and C1 are the cells we want to test for being blank in D1 and E1 contains the relevant text:

    =IF(OR(LEN(A1),LEN(B1),LEN(C1)),E1,"")

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-29 07:49, SharonDrumm wrote:
    I am trying to create a formula in a new cell that says if any of three other cells are not blank, then fill the contents of new cell with same text as in a different cell, otherwise don't put anything in the new cell.

    I can't write the correct formula. What's more, I cannot figure out how to cycle through more than one set of the formulae in each cell. This is important because I don't want to leave a blank cell (and therefore in this instance, a blank row) for every instance when the argument is true (all are blank).

    Any help is really appreciated. My skills are just not this developed, although I can conceive of what I want to do!

    Sharon
    Hi Sharon:
    We need a little bit of more specificity here -- "when you say any of three other cells are not blank" -- Do you mean all three (each one of them is not blank), or any (any one of them is not blank).
    Well anyway, here is a small example I have for you to consider:
    A5=Great!
    Cell C5=1
    D5=2
    E5=3
    in cell F5 I am going to write a formula that checks whether all (each one of C5, D5, and E5) is blank -- if this condition is met, put in the value in cell A5 -- otherwise leave it blank

    '=IF(AND(ISBLANK(C5),ISBLANK(D5),ISBLANK(E5)),A5,"")

    HTH
    Please post back if it works for you ... otherwise explain a little further and let us take it from there!

    _________________
    Yogi Anand

    Edit: Deleted inactive website from hardcoded signature

    [ This Message was edited by: Yogi Anand on 2003-01-19 14:16 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Assuming data is in cells a1,a2,a3, and cells to reference are a4 and a5


    =IF(AND(ISBLANK(A1),ISBLANK(A2),ISBLANK(A3)),A4,A5)

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sheesh....you "MASTERS" are quick!!

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    Yogi & wi_guy,

    Put in one of the target cells,

    =IF(ZX,1,"") [ ZX must be an "untouched" cell ]

    then try the ISBLANK formula you suggested.

    Aladin

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-29 08:17, Aladin Akyurek wrote:
    Yogi & wi_guy,

    Put in one of the target cells,

    =IF(ZX,1,"") [ ZX must be an "untouched" cell ]

    then try the ISBLANK formula you suggested.

    Aladin
    Hi Aladin:
    I see your point that with the scenario you proposed, the affected target cell would appear to be blank -- but it really wouldn't be blank -- it will have a formula in it. So the formula that my cyber-buddy wi_guy and I proposed works as expected. I hope I am not missing some thing here!

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-03-29 08:46, Yogi Anand wrote:
    On 2002-03-29 08:17, Aladin Akyurek wrote:
    Yogi & wi_guy,

    Put in one of the target cells,

    =IF(ZX,1,"") [ ZX must be an "untouched" cell ]

    then try the ISBLANK formula you suggested.

    Aladin
    Hi Aladin:
    I see your point that with the scenario you proposed, the affected target cell would appear to be blank -- but it really wouldn't be blank -- it will have a formula in it. So the formula that my cyber-buddy wi_guy and I proposed works as expected. I hope I am not missing some thing here!
    In A1 enter:

    =IF(ZX,1,"") [ ZX must be an "untouched" cell ]

    B1 is a untouched cell (nothing in it)

    C1 is a untouched cell (nothing in it)

    In E1 enter: Great

    In D1 enter:

    =IF(AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)),E1,"")

    Note. What Sharon wants is I think something different, but that's not the issue here.

    What do you get?

    Aladin


  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    IF(AND(ISBLANK(C5),ISBLANK(D5),ISBLANK(E5)),A5,"")
    HTH
    Please post back if it works for you ... otherwise explain a little further and let us take it from there!

    _________________
    Yogi Anand
    ANAND Enterprises
    http://www.handtech.com/anand yogia@hotmail.com

    [ This Message was edited by: Yogi Anand on 2002-03-29 08:07 ]
    [/quote]

    Thank you! This worked. Now, the second part of my problem is not fixed. The idea is that on one worksheet, I have a checklist users will fill out. If certain criteria are met (some columns are checked), then I want that row value to appear on the next worksheet, where the user will go through another check-off exercise.

    Using the formula you describe above, I can fill in the cells down one column, referencing the work done on the previous worksheet. Problem is, if none of the criteria are met for one cell, the whole row is left blank. Do you get my drift? If the criteria are met for only one observation on the previous worksheet, I could conceivably have 4 rows filled in interspersed with 20 blank rows on the new worksheet.

    Any thoughts on this problem? Again, thanks for the formula!

    Sharon

    [ This Message was edited by: SharonDrumm on 2002-03-29 09:41 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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