+ Reply to Thread
Results 1 to 7 of 7

Reference cell formula returning 0 when BLANK, need BLANK to show

  1. #1
    Registered User
    Join Date
    03-31-2016
    Location
    Washington
    MS-Off Ver
    2007
    Posts
    3

    Reference cell formula returning 0 when BLANK, need BLANK to show

    I'm trying to pull data from another excel sheet - my problem is that when I use my formula < =VALUE('Sheet 1'!AH5) > and AH5 is blank, it is filling in a zero in the new cell. This is a problem because I am using the referenced data in an AVERAGE formula and it is throwing off my numbers (because the zero is counted in the average instead of being left blank). Is there any way I can pull the information from the other sheet and have it fill in blanks when the original cells are blanks?
    Last edited by williamc; 03-31-2016 at 04:19 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Reference cell formula returning 0 when BLANK, need BLANK to show

    You can do this

    =IFERROR(1/(1/VALUE('sheet 1'AH5)), "")
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    03-31-2016
    Location
    Washington
    MS-Off Ver
    2007
    Posts
    3

    Re: Reference cell formula returning 0 when BLANK, need BLANK to show

    This may be obvious (I'm still new to Excel), but would I add that to my existing formula or make it the new formula? If I add/combine it with the one I have, how do I do that?

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    43,672

    Re: Reference cell formula returning 0 when BLANK, need BLANK to show

    That would replace your formula. Look closely; your formula is embedded.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    03-31-2016
    Location
    Washington
    MS-Off Ver
    2007
    Posts
    3

    Re: Reference cell formula returning 0 when BLANK, need BLANK to show

    Thank you. So that worked with the blanks. The problem I'm having now is that the zero's are also showing up as blanks, and I need the zero's to show up as zero's. How would I alter that to show zero as zero and blank as blank?

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Reference cell formula returning 0 when BLANK, need BLANK to show

    If you have to differentiate between blank and 0, try

    IF('sheet 1'AH5="","",VALUE('sheet 1'AH5))

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Reference cell formula returning 0 when BLANK, need BLANK to show

    As an aside, why are you using VALUE? Are some (or all) of the numbers on sheet 1 set as text? Do you have some text values that you want to show up as errors?

    If all the data is numbers (or blanks)
    =IF('sheet 1'AH5="","",'sheet 1'AH5)

+ 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. [SOLVED] Returning a blank in a cell if the referenced cell is blank (with a formula) itself
    By milkychips in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2016, 08:57 AM
  2. need to show reference cell blank if formula returns a $0.00 value
    By AWITCHER in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2014, 10:48 AM
  3. [SOLVED] If function returning incorrect value reference cell value is blank
    By lbrannon in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-23-2013, 09:40 AM
  4. If formula to return blank if reference cell blank
    By dhooper in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-10-2013, 08:56 PM
  5. Replies: 2
    Last Post: 11-10-2012, 10:49 AM
  6. Replies: 5
    Last Post: 03-23-2011, 07:09 PM
  7. Replies: 1
    Last Post: 09-23-2010, 01:24 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