We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Excel Formular

Options
Hi
I'm trying to get a comment from a formula into a specific cell.


In the desired cell I want to read "Incomplete" if any of the cells before it are blank.


So far I have


=IF(ISBLANK(A5:K5),”Incomplete”)


The comment that is appearing in the box is "False"


Where am I going wrong???

Comments

  • Stompa
    Stompa Posts: 8,375 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    Would:

    =IF(COUNTBLANK(A5:K5)>0,"Incomplete","")

    work for you?
    Stompa
  • Consumerist
    Consumerist Posts: 6,311 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    Verb wrote: »
    So far I have

    =IF(ISBLANK(A5:K5),”Incomplete”)

    The comment that is appearing in the box is "False"
    Where am I going wrong???
    The format for the IF( ) command is:

    =IF(ISBLANK(range), action if true, action if false)

    You haven't included an action for the IF returning false.
    >:)Warning: In the kingdom of the blind, the one-eyed man is king.
  • @Consumerist is correct you need a statement for the false result othewise it will be incomplete or false on the record
  • Verb
    Verb Posts: 227 Forumite
    Part of the Furniture 100 Posts Combo Breaker
    Stompa wrote: »
    Would:

    =IF(COUNTBLANK(A5:K5)>0,"Incomplete","")

    work for you?





    Worked a treat thank you very much.




    Just two more to add


    How do I get it to say complete if all cells before are filled in


    And


    I don't want this formula to start until A5 has data in
  • Consumerist
    Consumerist Posts: 6,311 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    edited 4 February 2016 at 4:33PM
    Verb wrote: »
    . . .
    How do I get it to say complete if all cells before are filled in
    And
    I don't want this formula to start until A5 has data in

    =IF(COUNTBLANK(A5:K5)>0,"Incomplete","Complete")

    and

    =IF(ISNUMBER(A5), IF(COUNTBLANK(A5:K5)>0,"Incomplete","Complete"), "" )

    Edit
    Sorry, missed the final close bracket - now added.
    Sorry again, the edit window splits the formula so a bit tricky to make sure the format is correct. The first IF statement above is enclosed in the second IF statement to test cell A5.
    >:)Warning: In the kingdom of the blind, the one-eyed man is king.
  • Verb
    Verb Posts: 227 Forumite
    Part of the Furniture 100 Posts Combo Breaker
    =IF(COUNTBLANK(A5:K5)>0,"Incomplete","Complete")

    and

    =IF(ISNUMBER(A5), IF(COUNTBLANK(A5:K5)>0,"Incomplete","Complete") )

    Edit
    Sorry, missed the final close bracket - now added





    Thank you spotted that.


    It says False now, is there a way to get rid of that??
  • Stompa
    Stompa Posts: 8,375 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    Verb wrote: »
    It says False now, is there a way to get rid of that??

    =IF(ISNUMBER(A5), IF(COUNTBLANK(A5:K5)>0,"Incomplete","Complete"),"")
    Stompa
  • Verb
    Verb Posts: 227 Forumite
    Part of the Furniture 100 Posts Combo Breaker
    Stompa wrote: »
    =IF(ISNUMBER(A5), IF(COUNTBLANK(A5:K5)>0,"Incomplete","Complete"),"")



    Thank you


    You have been a great help


    My head has fallen off today
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 350.9K Banking & Borrowing
  • 253.1K Reduce Debt & Boost Income
  • 453.5K Spending & Discounts
  • 243.9K Work, Benefits & Business
  • 598.7K Mortgages, Homes & Bills
  • 176.9K Life & Family
  • 257.1K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.