Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Testing For Zero (Excel 97-SR2)

    How can you test, without VBA, if the user has actually entered 0, as opposed to and empty cell that just happens to evaluate to zero? I need to do something like:

    IF(ISNUMBER(Sheet1!A1),Sheet1!A1,Sheet2!A1)

    Where ANY number 0 or greater is valid and therefore TRUE, but ONLY if the user has really entered a zero. Put another way, if the user presses DELete in cell Sheet1!A1, I want the above test to return FALSE. Make sense?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Testing For Zero (Excel 97-SR2)

    In my Excel 97 SR2, ISNUMBER(cell) returns FALSE if the cell is empty.
    If Sheet1!A1 is empty, your formula is equivalent to =Sheet2!A1; this evaluates to 0 if Sheet2!A1 is empty.
    Perhaps you need
    IF(ISNUMBER(Sheet1!A1),Sheet1!A1,IF(ISNUMBER(Sheet 2!A1),Sheet2!A1,""))

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing For Zero (Excel 97-SR2)

    Hi Hans

    You are of course correct and I of course am a thicket! ISNUMBER(cell) returns FALSE if there is no number in a cell. What it can't account for however is my inability to reference the correct sheet! Dooooh! <img src=/S/woops.gif border=0 alt=woops width=58 height=36>

    Regards
    Peter

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing For Zero (Excel 97-SR2)

    Can't you just type =ISNUMBER( and then navigate to the other sheet, select the cell and press enter?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Testing For Zero (Excel 97-SR2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Peter

    OK silly question but why can't you <<< <font color=red>my inability to reference the correct sheet! Dooooh! </font color=red>>>>

    What is stopping you from referencing the correct sheet?

    Is the correct sheet in the same workbook, is it hidden, or is it protected?

    Thanks for answering, this way we can have a complete picture of your problem.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing For Zero (Excel 97-SR2)

    Hi Folks

    For 'inability' read 'stupidity' :-(

    Thanks for everyone's time though.

    Regards
    Peter

Posting Permissions

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