• Blank from referenced cell came in as zero (Excel 97)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Blank from referenced cell came in as zero (Excel 97)

    Author
    Topic
    #358648

    In column F, we have a cell whose data comes via = a cell from another sheet in the workbook. The cell in the other sheet is blank, formatted general. When the data comes into the cell in column F, it shows 0. The cell in column F is also formatted general.

    Lower in column F, there are cells with 0.00 or other numbers, and we want the zero values to show as zeros, not blanks, so we do not want to untick the zero values box in Options. We tried setting the sending and receiving cells to text format, but that made no difference.

    What should we be looking at to make the cell in column F look identical to the cell from which the data came?

    Viewing 2 reply threads
    Author
    Replies
    • #535562
      =IF(Sheet2!F1="","",Sheet2!F1)
      
      • #535687

        Thanks, Legare. I’m not sure I understand what this says. There might be an alpha value in Sheet2!F1. It could have a value or be blank. Does this copy the cell only if it’s blank, or does it always copy it but if it’s blank turn it into a blank? We need it to do the latter.

        • #535755

          The IF statement checks to see it the cell being copied is empty (“”) and if it is it puts a null string (the second “”) in the cell and if it is not empty then it copies the source cell.

    • #535686

      But if the value in a cell is =Sheetname!F4, why would the value be different in the cell that references the other cell (one a space, one a blank)? If there’s something about the cell doing the reference that converts what comes in, what is it? Not cell format, since they appear to be the same.

    • #535756

      If the cell being copied contains a blank, then that is what you want to copy. My formula does what it should in both cases.

      • #535894

        Thanks, Legare. I don’t know much Excel. I’ll bet you’re right about this, but all I see in the cell now instead of the 0 is the formula that I keyed in from what I thought you wrote: =IF(Mandatory!C30=””,””,Mandatory!C30) (I’m using the right cell references now). The idea was to see a blank. I have formulas UNticked on all sheets in Tools | Options | View.

        Actually, I made a copy of the workbook. Here’s exactly what’s there. In the original, Summary!C2 has a formula: =Mandatory1!C30. Mandatory1!C30 is right now blank. But cell Summary!C2 shows 0. In the copy, Summary!C2 is where I have your formula, which shows the formula itself, where the formula =Mandatory1!C30 used to show a 0.

        I wonder if there’s just something wrong with the workbook. In another cell, I have different things showing in one of the corresponding cells in the original and the copy. In the original, Summary!B2 shows in the keying area on the toolbar a formula: =Mandatory1!B30. In the cell, it shows the formula from Mandatory1!B30: “=IF(B4=”Yes” and B7=”Yes”,”Yes”,”No”)”. In my copy of the workbook, both the cell and the keying area for cell Summary!B2 show: =Mandatory1!B30. I had tried your formula there and then changed it back to what it was. Why would this be different in the two workbooks?

        All the B2 and B30 cells are formatted as text. The C30 cells are general. It hasn’t made a difference whether the C2 cells were text or general. I really have checked many times for the Formulas option, and it really is off everywhere.

        • #535898

          Was the cell formatted as Text before you entered the formula into it? If so, it will display the formula. I see in you message that you say that the cells are formatted General. However, the question is what was the format when the formula was entered. If you change the formula from text to general, then you have to select the cell, press F2 to get into edit mode, and then press Enter to change the text to a formula.

          If that didn’t help fix the problem, could you attach a censored copy of the file that we can look at?

          • #535900

            Legare, that’s so cool. Now it’s blank and I can get B2 to say the same thing in the cell and the entry area. Thanks very much for your time and the explanation.

    Viewing 2 reply threads
    Reply To: Blank from referenced cell came in as zero (Excel 97)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: