+ Reply to Thread
Results 1 to 11 of 11

Help with ISBLANK function referring to a cell with another ISBLANK formula

  1. #1
    Registered User
    Join Date
    03-22-2010
    Location
    massachusetts
    MS-Off Ver
    Excel 2007
    Posts
    19

    Help with ISBLANK function referring to a cell with another ISBLANK formula

    Hi there,
    I have attached a spreadsheet to help you see what I am referrign to:

    I am trying to get column J to be blank until there is data in cells A, B, C or H.

    Cells A, B, C and H contain IF(ISBLANK) formulas, but so does column J, so I am wondering if it is not *truly* blank if there is a formula there and that's why it is populating "Action" all the way down the column?

    Any help would be greatly appreciated!

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,529

    Re: Help with ISBLANK function referring to a cell with another ISBLANK formula

    A Null String ("") is a 0 Length Text string - ISBLANK (like COUNTA) treats Null Strings as non-blanks whereas COUNTBLANK for ex. does not.

    Regards J

    Please Login or Register  to view this content.
    I confess I've not reviewed your formula in Col K but I suspect you can streamline - what I would say is:

    1. avoid using SUMPRODUCT with entire column references as the function is inefficient

    2. use SUMIFS in preference to SUMPRODUCT if viable (as is seemingly the case here)

  3. #3
    Registered User
    Join Date
    03-22-2010
    Location
    massachusetts
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Help with ISBLANK function referring to a cell with another ISBLANK formula

    I tried the formula you suggested and it says I've entered too many arguments?

    The sumifs does not work for my column K formula. it says too few arguments have been entered.

    I'm not sure if I should head the macro-writing route or not. IN which case I'd need further help, I'm guessing.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,529

    Re: Help with ISBLANK function referring to a cell with another ISBLANK formula

    Oops - sorry about that - COUNTBLANK accepts only contiguous ranges

    Please Login or Register  to view this content.
    If you want people to review Col K please indicate as such and outline expected results etc...

    Regards SUMIFS - from looking at your SUMPRODUCT just now you should be using COUNTIFS...
    you can replace your three ISBLANK tests within the AND with A2&B2&C2=""
    Last edited by DonkeyOte; 03-23-2011 at 09:21 AM.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,529

    Re: Help with ISBLANK function referring to a cell with another ISBLANK formula

    Without having looked at set up of criteria sheet

    Please Login or Register  to view this content.
    should prove a lot more efficient than the pre-existing approach but I am sure with more time & thought it could be streamlined further.

    (COUNTIFS/SUMIFS are both sufficiently efficient that using entire column references is no big deal - it's the quantity of these formulae being executed that will affect calculation time)

  6. #6
    Registered User
    Join Date
    03-22-2010
    Location
    massachusetts
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Help with ISBLANK function referring to a cell with another ISBLANK formula

    that formula is now giving me a cell value of 1?

    Basically what I need is for my techs to just enter the sample site and bacteria counts and have the formulas do the rest. I have made tables on the criteria tab that will populate columns A, B and C and then I have a formula that calculates the total amount for the bacteria if it is an air sample. And I have gotten the formulas to work for that purpose, but it populates "Action" even when the cells are blank for whatever reason. So that's where I need your expertise with the ISBLANK function.

    I'm new to the formula business and I'd appreciate any troubleshooting you can provide.

    thanks!

  7. #7
    Registered User
    Join Date
    03-22-2010
    Location
    massachusetts
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Help with ISBLANK function referring to a cell with another ISBLANK formula

    sorry that previous response was referring to the formula in your post #4.

    and i tried the other formula you provide (in post #5) and i get the cell to populate "Alert" (when all other cells are blank, cell K should also be blank.

    thanks!
    Last edited by camdameron; 03-23-2011 at 09:30 AM.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,529

    Re: Help with ISBLANK function referring to a cell with another ISBLANK formula

    Quote Originally Posted by camdameron View Post
    that formula is now giving me a cell value of 1?
    Whereas it should be.... ?

    I adapted the formula you had in place in Col J which tested A & H - if you want to add additional cells to the check (per your OP - ie B & C) then:

    Please Login or Register  to view this content.
    the above would leave J blank until such time as A,B,C & H were neither blank nor null strings

    However, it should be noted that for J2 the result would still be 1 given all referenced cells contain data.

    Quote Originally Posted by camdameron
    i tried the other formula you provide (in post #5) and i get the cell to populate "Alert" (when all other cells are blank, cell K should also be blank.
    Please post a further sample file to illustrate [include also expected results] as I am not able to replicate the above I'm afraid.
    To confirm the formula for column K... presently if A:C are ALL blank/null strings then the result is also null string (rather than Alert) - this apes the logic of your original.
    If you need to add H to this append the concatenation accordingly - A2&B2&C2&H2=""

  9. #9
    Registered User
    Join Date
    03-22-2010
    Location
    massachusetts
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Help with ISBLANK function referring to a cell with another ISBLANK formula

    So, what I'm trying to accomplish with this sheet is to have a tech enter in a sample site in column E, which will populate the cells in columns A, B and C (based on the information I have already established in columns A, B and C on the criteria tab). Columns A, B and C should be empty until a value is entered into column E. Column J will also be empty until a value is entered into column H, which will then calculate the correct total CFU based on whether it is an air or surface sample. Column K should be empty until there are values in A, B, C and H which it will then calculate whether it is an alert or action based on the criteria I have already established on the criteria tab.
    The problem I am running in to is that when the cells in A, B, C and H are all blank, the formula still populates "Action" in column K, when I need it to be empty.


    If you note that K2 through K11 all have properly populated cells (blank when it does not meet the criteria and Alert or Action when it does) but for the cells where there is no data entered in cells A-J, it still populates "action" in the K cell.

    The expected results are that an entire row with no data (besides the formulas) should have a blank column K (cell K13 is what it should look like (i've removed the formula to show what it should look like).
    Does this help?
    Attached Files Attached Files

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,529

    Re: Help with ISBLANK function referring to a cell with another ISBLANK formula

    The formulae / suggested amendments provided previously should suffice - for clarity I will repost:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-22-2010
    Location
    massachusetts
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Help with ISBLANK function referring to a cell with another ISBLANK formula

    i'm a bonehead.
    IT WORKED!!!!

    thank you so much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1