+ Reply to Thread
Results 1 to 9 of 9

0 value when linking to a blank cell

  1. #1
    Registered User
    Join Date
    02-26-2010
    Location
    Crawley, West Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    0 value when linking to a blank cell

    I am running a football scores competition. I have a sheet which will contain all the results as they occur. As yet the competition has not started so all scores are blank.
    But when I link the scores to another sheet it shows as 0-0 which looks like the match has been played and the score is 0-0.
    i.e. results sheet shows "Team A Team B " but the cell that links to it shows "Team A 0 Team B 0"
    I have seen a work round whereby you set the cell fromat to custom and then type in 0;-0;. This is allows the cell to show blank instead of 0, but there is a problem. The cell now cannot show 0 even when a 0 is typed in to the results sheet.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    Re: 0 value when linking to a blank cell

    What is the formula that you are using to populate the scores on this sheet?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,896

    Re: 0 value when linking to a blank cell

    Maybe this?

    =if(your-formula="","",your-formula)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    02-26-2010
    Location
    Crawley, West Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: 0 value when linking to a blank cell

    No formula. there are currently no scores on the results sheet.
    all the fixtures are in the results sheet. Currently blank as its next years world cup.
    There is a separate sheet for each player.
    So in the appropriate place on each players sheet i put =Results!B4 for example.
    But whereas the cell in Results sheet is blank, the cell in the players sheet shows 0.
    When results are blank in the results sheet it needs to be blank in the players sheet.
    If this does not help I may be able to send my workbook or a sample.
    Thanks for replying.

  5. #5
    Registered User
    Join Date
    02-26-2010
    Location
    Crawley, West Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: 0 value when linking to a blank cell

    Thanks. I'm sure this will work but I feel sure it should be able to be done without formula.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,899

    Re: 0 value when linking to a blank cell

    Try the formula in post #3. That is:

    =IF(Results!B4="","",Results!B4)

    Edit: Just saw your last post. You already have a formula there so i'm not sure why you don't want to simply edit the formulas.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,896

    Re: 0 value when linking to a blank cell

    Quote Originally Posted by bakert View Post
    Thanks. I'm sure this will work but I feel sure it should be able to be done without formula.
    If you want to pull info from another cell (if I understand your request), other than using VBA, there is no other way to pull the into, than using a formula

  8. #8
    Registered User
    Join Date
    02-26-2010
    Location
    Crawley, West Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: 0 value when linking to a blank cell

    Ok thanks. Looks like I am going use the formula. I thought it should be easy as typing = then clicking in the cell I wanted to link to. I have something in the back of my mind from a tutorial where you can set a cell or range to "allow null", but can't find anything on help. It may be something only on Access DB.
    Thanks for trying
    Trevor.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,896

    Re: 0 value when linking to a blank cell

    Quote Originally Posted by bakert View Post
    I thought it should be easy as typing = then clicking in the cell I wanted to link to.
    That is/would be a formula

    set a cell or range to "allow null"
    That may be access, but in excel, you can set up Data validation to only allow specific entries

+ 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. How to avoid a 0 when linking to a blank cell with formatting
    By CantripGhost in forum Excel General
    Replies: 9
    Last Post: 03-04-2014, 10:23 AM
  2. Replies: 3
    Last Post: 03-03-2014, 12:34 PM
  3. Replies: 2
    Last Post: 05-31-2013, 08:45 AM
  4. [SOLVED] Tell whether a cell is Blank or No - when linking two Excel Files
    By Linraen7 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2012, 08:58 AM
  5. Linking cells in different sheets, only when the cell is not blank
    By BRawr91 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-30-2011, 08:53 PM
  6. 0 Appears for Linking Blank Cells
    By jcavigli in forum Excel General
    Replies: 10
    Last Post: 05-25-2008, 03:21 PM
  7. Replies: 3
    Last Post: 02-11-2007, 10:17 AM

Tags for this Thread

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