Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel 2010 workbook that contains a number of individual worksheets. The cells on one of the sheets are linked to individual cells on two other worksheets in the same workbook. I'm using a direct cell reference that essentially says that whatever value is entered into a particular cell on one sheet also populates cells on two other sheets. I used the (=) function with the cell reference to accomplish this.

The issue I'm running into is that, even when the primary cell is left blank, the cells that populate from that primary cell will display 0, rather than remaining blank themselves.

I want the subordinate cells to remain blank if the primary cell they're linked to is blank.

share|improve this question
add comment

2 Answers

Here are three answers:

1) Letting other.cell.reference represent the reference formula that you currently have after the = (e.g., Sheet17!$H$42), replace that link reference with

=IF(other.cell.reference<>"",other.cell.reference, "")

2) Set the “Number” format of your linked cells to “Custom”: General;–General;.

3) In “Excel Options”, “Advanced” page, “Display options for this worksheet” section, clear the “Show a zero in cells that have a zero value” checkbox.  Warning: this will cause all zeroes in the worksheet to disappear.

share|improve this answer
    
The first option is sub-optimal, as it requires entering (or copying) your formula twice within the cell. It can be especially nasty when you have a really long formula, and then need to edit (or worse, debug) that formula later. The second option doesn't seem to be working for me, but maybe I'm doing it wrong. The third is definitely not ideal since the scope of its effect is much broader than one might truly desire. Are there really no better options? –  Iszi Sep 18 '13 at 18:32
    
@Iszi: Regarding the first option: I’m not talking about entering a formula twice. As per the original question, I’m talking about a situation where Q1 contains a (really long) formula and A1 contains =Q1; we want to change A1 to =IF(Q1<>"", Q1, ""), which you should never need to change again. “Are there really no better options?” Well, this question has been idle for nine months, and this is all that has been posted. If these answers aren’t good enough for you, post a new question, or put a bounty on this one. –  Scott Sep 18 '13 at 19:08
    
That's more or less the same thing. Though it does resolve some of the cumbersomeness of having to troubleshoot two instances of one formula within one cell, it also complicates things a bit by adding another (otherwise unnecessary) cell or group of cells to the mix. Unfortunately, I suppose there really isn't another "nice and clean" answer for this, though. –  Iszi Sep 18 '13 at 19:52
    
I did post a related question awhile back and the only worthwhile answer that's different from what's here involved a VBA script. –  Iszi Sep 18 '13 at 19:52
add comment

If the linked cell is non-numeric you can use an IF statement with ISTEXT:

=IF(ISTEXT(Sheet1!A2), Sheet1!A2, "")
share|improve this answer
    
But if the referenced cell (Sheet1!A2, in your example) contains a number, this will display a null string. –  Scott Dec 6 '12 at 21:35
    
Yeah it wasn't clear from the question what data is in the referenced cell. I figured if it was numeric he would want the zeros. –  Brad Patton Dec 6 '12 at 21:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.