Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, BC
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <pre>=IF(Sheet2!F1="","",Sheet2!F1)
    </pre>

    Legare Coleman

  3. #3
    Tyrany
    Guest

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

    In addition to what Legare has said, be careful about the actual value of the cell. Excel sees "" as a different value to " ". The former is a blank, and the latter is a space.

    Unfortunately, Excel doesn't represent that difference. All cells that are either blank, or containing a space look the same.

    Maybe MS should make cells containing spaces only show as "{spaces only} " in the edit field?

    Have fun!

    David

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, BC
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, BC
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  6. #6
    Tyrany
    Guest

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

    I have noticed that sometimes whe you link a cell, the format of the source cell isn't copied over. Maybe try a COPY from the source cell, and a PASTE SPECIAL/FORMATS in the destination cell.

    Dunno for sure - just an idea...

    Good luck,

    David

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Legare Coleman

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, BC
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?
    Legare Coleman

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, BC
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •