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
    
My formula is very long, for me also the IF construction is hardly acceptable, good question but I too did not find good enough answer. {= IFERROR( INDEX(NEPŘÍMÝ.ODKAZ("EsZkouska");SMALL(KDYŽ((INDEX(NEPŘÍMÝ.ODKAZ("EsZkouska");;1‌​;1)="ČSN721180")*(INDEX(NEPŘÍMÝ.ODKAZ("EsZkouska");;9;1)="RC_P_B");ŘÁDEK(NEPŘÍMÝ.‌​ODKAZ("EsZkouska"))-MIN(ŘÁDEK(NEPŘÍMÝ.ODKAZ("EsZkouska")))+1;"");1);17;1);"")} –  VDohnal Jul 25 at 11:32
    
It is possible to use IF construction combined with this approach: stackoverflow.com/questions/22359452/…. Like this you can create named formulas and use them in your simple IF statement... –  VDohnal Jul 25 at 11:42

4 Answers 4

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

I too did not find a better solution than the Scott's one.

But combined with the approach from here it could be almoste bearable, I think:

http://www.ozgrid.com/Excel/named-formulas.htm

Let's say I have formula like this

= IFERROR( INDEX(INDIRECT("EsZkouska");
  SMALL(IF((INDEX(INDIRECT("EsZkouska");;1‌​;1)="ČSN721180")*(INDEX(INDIRECT("EsZkouska");;9;1)="RC_P_B");
  ROW(INDIRECT"EsZkouska"))-MIN(ROW(INDIRECT("EsZkouska")))+1;"");1);17;1);"")

This formula reads cell value from a data sheet using conditional select and presents them on another sheet. I have no control over cell formating on the data sheet.

I go to Insert > Name > Define and in the "Names in workbook" I create new Name "RC_P_B". Then into the "Refers to" field I copy my formula (without {} characters - it is array formula).

Then you can use Scott's formula whithout having to repeat the whole formula text:

 {=IF(RC_P_B<>""; RC_P_B;"---")}

I believe this is better than to copy the whole formula.

share|improve this answer

I simple don't use a formula to overcome this problem. All I do is to conditionally format cells to font color white if cell value equals 0.

share|improve this answer

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
1  
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

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.