+ Reply to Thread
Results 1 to 6 of 6

Return a Blank instead of 0 if cell refernce is blank

  1. #1
    Registered User
    Join Date
    10-09-2016
    Location
    Preston, England
    MS-Off Ver
    2010, 2013 & 2016
    Posts
    30

    Question Return a Blank instead of 0 if cell refernce is blank

    Hi,
    I have a template where sheet 1 is for entering data and sheet 2 is formatted and has formulas to work out calculations.
    the tables in each sheet are set to 210 rows with row 1 in each being the header.

    In a scenario there could be 100 rows of data entered and then saved as another file or all 210 rows of data entered and then saved as. The original document is a template to be used time and time again.

    the issue I am having is in sheet two, when there is only 100 rows of data entered in sheet 1 I am getting 110 "0s" in the remaining rows which is messing with my count calculations. Some of the data entered might be a 0 so these count towards the calculations.

    I am racking my brains on trying to get the cell reference formula to return a blank.

    this is the simple reference formula in the cells in sheet 2.

    Please Login or Register  to view this content.
    Can some one add to this (I know its really simple yet I just cant get it to work and I cant seem to fine anything else where).

    Thanks in advance
    Last edited by Jwilf; 10-19-2016 at 04:27 PM. Reason: Solved

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Return a Blank instead of 0 if cell refernce is blank

    how about =if('Sheet1'!G3="","",'Sheet1'!G3)
    or if zeros are the issue in sheet1 this would work..
    =if('Sheet1'!G3="0","",'Sheet1'!G3) if text
    =if('Sheet1'!G3=0,"",'Sheet1'!G3) if numeric
    Last edited by Sam Capricci; 10-19-2016 at 04:08 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    10-09-2016
    Location
    Preston, England
    MS-Off Ver
    2010, 2013 & 2016
    Posts
    30

    Re: Return a Blank instead of 0 if cell refernce is blank

    The top formula worked perfect thank you

    I was trying =if('Sheet1'!G3<>"") and was just not getting it.

    Cheeres

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Return a Blank instead of 0 if cell refernce is blank

    You're welcome and thank you for the rep!

  5. #5
    Registered User
    Join Date
    10-09-2016
    Location
    Preston, England
    MS-Off Ver
    2010, 2013 & 2016
    Posts
    30

    Re: Return a Blank instead of 0 if cell refernce is blank

    One Last question and I will leave you alone.

    Now that formula is in, if I want to use this =COUNTA(Table1[English Language]) and ignore the blank returns so it only counts the cells that have a value and does count the formulas what would I need to add to this formula?
    Table 1 is the table in sheet 2 where the formula you gave me is used. English language is the column title.

    Cheers

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Return a Blank instead of 0 if cell refernce is blank

    COUNTA will count the formula, try just using COUNT, that should ignore formulas and blanks.

+ 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. Replies: 1
    Last Post: 07-22-2016, 04:03 PM
  2. Return first non blank cell (cells have formulas that return blank)
    By BG1983 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-05-2016, 04:06 PM
  3. Index/ Match Formula, How to return blank cell as a blank not 0
    By MDResearcher in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-24-2016, 08:40 PM
  4. Replies: 10
    Last Post: 09-12-2015, 02:04 PM
  5. [SOLVED] When my formula encounters a blank cell it returns a 0, I need it to return a blank
    By stretch99 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2014, 07:52 PM
  6. [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
  7. [SOLVED] Need formula to return blank instead of #N/A when source cell is blank, using VLOOKUP
    By TMB1234 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2013, 07:16 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