+ Reply to Thread
Results 1 to 13 of 13

Cell is blank but ISBLANK function says its not blank

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Cell is blank but ISBLANK function says its not blank

    I have this file...........see enclosed........cell A1 looks blank........but the function ISBLANK in C1 indicates that the cell is NOT blank.

    any ideas?
    Attached Files Attached Files
    Last edited by welchs101; 06-29-2011 at 06:20 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Cell is blank but ISBLANK function says its not blank

    The ISBLANK() function can be fooled by things under the hood like autofilter extract remnants and I'm sure many other things.

    You can test A1 this way to get a true answer:

    =A1=""
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Cell is blank but ISBLANK function says its not blank

    That might be zero length null string., like when we use in formulas =""

    If you use this in C1,

    =A1=""

    will give you TRUE. If you want to convert select the range go to;

    Data >> Text to Columns

    Then click finish.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Cell is blank but ISBLANK function says its not blank

    the problem is when i use the countA function it "counts" this blank cell as NOT blank.

    not sure how to get around this.

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Cell is blank but ISBLANK function says its not blank

    anyone have any ideas?

  6. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Cell is blank but ISBLANK function says its not blank

    If the range contains only text not numbers (mixed alphabets & numbers OK), you can use this formula.

    =COUNTIF(A:A,"?*")

    If the range mixed with numbers & texts try this;

    =SUM(COUNTIF(A:A,{"?*","<9E300"}))
    Last edited by Haseeb Avarakkan; 06-28-2011 at 11:30 PM.

  7. #7
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Cell is blank but ISBLANK function says its not blank

    that works! thanks!

  8. #8
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Cell is blank but ISBLANK function says its not blank

    when i use that formula in a defined name for plotting...........i am getting some really wierd results. i am enclosing a file for help explaining.

    WHen you open the file do the following:
    1) Change the named range "chrt_M_uat"
    From:
    OFFSET(Summary_UAT!$H$1,COUNTA(Summary_UAT!$H:$H)-1,0,-MIN(Summary_UAT!chrt_M_uat_len,COUNTA(Summary_UAT!$H:$H)-1),1)

    To:
    OFFSET(Summary_UAT!$H$1,SUM(COUNTIF(h:h,{"?*","<9E300"}))-1,0,-MIN(Summary_UAT!chrt_M_uat_len,SUM(COUNTIF(h:h,{"?*","<9E300"}))-1),1)


    2) Next try to update the graph as follows:
    click on one of the bars in the graph, then rt-click and goto select data. Then edit data for header1. Under teh series values change it from
    Summary_UAT!$I$2:$I$9
    to
    Summary_UAT!chrt_M_uat_A

    when you try to do this you get formula error. Now open up name manager and edit name "chrt_M_uat"...............its no longer the same? its somehow changed



    can anyone explain it?

  9. #9
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Cell is blank but ISBLANK function says its not blank

    forgot to attach file
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Cell is blank but ISBLANK function says its not blank

    if i marked this as solved...........should i start a new thread?

  11. #11
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Cell is blank but ISBLANK function says its not blank

    You have valid date in Col_H, just use COUNT instead of COUNTA, not necessary for COUNTIF(H:H,{"?*".....)

    Firstly define a name, Length

    =MAX(1,COUNT(Summary_UAT!$H:$H))

    Then change chrt_M_uat formula to;

    =OFFSET(Summary_UAT!$H$1,1,,MIN(chrt_M_uat_len,Length))

    Change $H$1 to $I$1, $J$1, $K$1, $L$1 for chrt_M_uat_A, B, C and D ranges.

    See the attached.

  12. #12
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Cell is blank but ISBLANK function says its not blank

    thanks.

    for some reason i keep getting an error

    "A formula in this worksheet contains one or more invalid references"

    any ideas why?

  13. #13
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Cell is blank but ISBLANK function says its not blank

    Change Length formula to;

    =MAX(1,MIN(COUNT(Summary_UAT!$H:$H),chrt_M_uat_len))

    Then change chrt_M_uat formula to;

    =OFFSET(Summary_UAT!$H$1,1,,Length)

+ 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