Closed Thread
Results 1 to 10 of 10

How to avoid a 0 when linking to a blank cell with formatting

  1. #1
    Registered User
    Join Date
    11-17-2010
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    12

    How to avoid a 0 when linking to a blank cell with formatting

    Here's the situation, i want cell B1 to be linked to cell A1, so that when i enter data in to cell A1 it appears in B1 as well. However, when A1 is blank, i do not want B1 to show a zero. I know the problem can be solved with the if() function, but I believe there must be a formatting option to solve this. is there one? I've attached a file for more information.
    Attached Files Attached Files
    Last edited by CantripGhost; 11-20-2010 at 05:56 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: How to avoid a 0 when linking to a blank cell with formatting

    Go to Excel options -> Advanced -> Display options for this worksheet and there uncheck box: Show a zero in cell that have a zero value

  3. #3
    Registered User
    Join Date
    11-17-2010
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: How to avoid a 0 when linking to a blank cell with formatting

    The pathing was a little different for Excel 2003 (under tools, and in the view tab) but you totally solved my problem, thanks!

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to avoid a 0 when linking to a blank cell with formatting

    or just custom format
    0;-0;;@
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: How to avoid a 0 when linking to a blank cell with formatting

    Here is the situation i am stuck with. I have two tabs in the excel attached, one is Linked tab and the other is Destination tab and
    here is what i want to see the result in the Linked tab -

    If cell D2 in Destination tab is blank, i want to see cell D4 in Linked tab blank without any zero or '-'.
    If cell E2 in Destination tab is zero or '-', i want to see cell E4 in Linked tab as zero or '-'.

    Could you please help resolving this?

    Thanks
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-18-2014
    Location
    France
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: How to avoid a 0 when linking to a blank cell with formatting

    Hello,
    hope this can help you,
    I putted =REPLACE('Destination tab'!D2,1,0,"") in linked tab!D4
    and =REPLACE('Destination tab'!E2,1,0,"") in linked tab! E4
    and got the result you were waiting for.

    With this function here i replace zero but you can do the same with anything.

  7. #7
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: How to avoid a 0 when linking to a blank cell with formatting

    You may use:
    =IF('Destination tab'!D2="","",'Destination tab'!D2)
    or:
    =IFERROR(('Destination tab'!D2&"")+0,"")

    Also, in future you must post your own question as new thread, please.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  8. #8
    Registered User
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: How to avoid a 0 when linking to a blank cell with formatting

    Thank you for your help! You guys really solved my problem.

    I will take care to add the new thread next time when i have questions.

  9. #9
    Registered User
    Join Date
    02-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    77

    Re: How to avoid a 0 when linking to a blank cell with formatting

    Quote Originally Posted by sweetusmile View Post
    Thank you for your help! You guys really solved my problem.

    I will take care to add the new thread next time when i have questions.
    Change the status to solved if you satisfied with this solution,
    Steps:
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: How to avoid a 0 when linking to a blank cell with formatting

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Closed Thread

Thread Information

Users Browsing this Thread

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

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