+ Reply to Thread
Results 1 to 5 of 5

If formula in one cell refers to another cell which is blank result is a blank cell

  1. #1
    Registered User
    Join Date
    07-01-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    44

    If formula in one cell refers to another cell which is blank result is a blank cell

    Hi all
    have another question in regard to my previous post, when the database is empty all cells in question are blank which is correct, but one column has FALSE in it, needs to be blank also with an empty database.

    In cell D3 I have the formula which works fine when data is entered
    Formula is =IF(COUNTIF($A$3:$A$8,G3),8,IF(COUNTIF($B$3:$B$8,G3),9,IF(COUNTIF($C$3:$C$8,G3),10)))

    I look forward to your help

    Thanks in advance
    M-Tommo

    I have attached the database
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: If formula in one cell refers to another cell which is blank result is a blank cell

    your formula is not testing for True or false

    =IF(COUNTIF($A$3:$A$8,G3),8,IF(COUNTIF($B$3:$B$8,G3),9,IF(COUNTIF($C$3:$C$8,G3),10)))

    COUNTIF($A$3:$A$8,G3)
    this will return a 0 or a number and 1 will use the true
    so needs something like
    COUNTIF($A$3:$A$8,G3)>0
    COUNTIF($A$3:$A$8,G3)=0
    COUNTIF($A$3:$A$8,G3)>4
    depending on what you want to do with the result

    you also do not have a final condition for FALSE
    Formula is =IF(COUNTIF($A$3:$A$8,G3),8,IF(COUNTIF($B$3:$B$8,G3),9,IF(COUNTIF($C$3:$C$8,G3),10, "need something here" )))

    if you do not put an entry where I have put "need something here"
    then you will get
    FALSE
    Last edited by etaf; 03-03-2014 at 08:06 AM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: If formula in one cell refers to another cell which is blank result is a blank cell

    Hi M-Tommo, is this what you're looking for?
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    _______________________________________________________________________________________________________________________________
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin

  4. #4
    Registered User
    Join Date
    07-01-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: If formula in one cell refers to another cell which is blank result is a blank cell

    Thanks Alvin,

    it was so simple, I have been playing around putting "" at front and back of formula for a while now with no luck, I feel like a fool now, I thought I'd done it that way but obviously didn't do it correctly.

    thanks again Alvin,

    and thanks to all who responded with suggestions

    Regards M-Tommo

  5. #5
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: If formula in one cell refers to another cell which is blank result is a blank cell

    You're welcome, and thanks for the reputation


    _______________________________________________________________________________________________________________________________
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Leave cell result blank if blank cell is in formula
    By Grind in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 09-28-2013, 06:15 AM
  2. [SOLVED] Cell referenced in formula has no information displayed (shows blank), return blank cell
    By nunayobinezz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-07-2013, 04:51 PM
  3. Replies: 6
    Last Post: 09-16-2010, 05:19 AM
  4. Blank cell if result of formula is zero
    By nergui77 in forum Excel General
    Replies: 1
    Last Post: 05-04-2008, 12:47 AM
  5. [SOLVED] How to get a formula result zero as blank cell
    By Excelerate-nl in forum Excel General
    Replies: 4
    Last Post: 11-22-2005, 12:40 PM

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