Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Empty Cell Reference In Another File (2000)

    I have two files. In one, on one of several worksheets I have a reference to a cell in a worksheet in the other file. When the cell that is referenced (the "first cell") is empty (i. e., no contents), the contents of the second cell that references the first cell result in a "zero". How can I make the second cell result in an empty cell as well, while still referencing the first cell?
    Thanks,
    Jeff

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty Cell Reference In Another File (2000)

    I think what you want is to put the following formula in a cell in your first workbook:

    =IF('[book1.xls]Sheet1'!$A$1=0,"",'[book1.xls]Sheet1'!$A$1)

    but the formatting options might be useful to you:

    tools / options / zero values
    format / cells / number / custom / #,##0.00;[ Red ](#,##0.00); (or similar),

    depending on whether you want the cell to be empty or just to appear empty.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Empty Cell Reference In Another File (2000)

    Thanks, Brooke, unfortunately some of the cells on the worksheet that is referenced contain zeros, and when I use youe formula, only with a => condition, I get the same unwanted results.

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty Cell Reference In Another File (2000)

    Sorry. Try this instead:

    =IF(isblank('[book1.xls]Sheet1'!$A$1),"",'[book1.xls]Sheet1'!$A$1)

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Empty Cell Reference In Another File (2000)

    Brooke,
    Thanks-it worked. However, when I tried it in referencing a cell on the same worksheet, it didn't seem to work. Any ideas?
    Thanks again,
    Jeff

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty Cell Reference In Another File (2000)

    so your formula looked like =IF(isblank(A1),"",A1)?

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Empty Cell Reference In Another File (2000)

    Yep!

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty Cell Reference In Another File (2000)

    Then I don't know whats going on. Any chance you could post the recalcitrant sample?

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Empty Cell Reference In Another File (2000)

    Leaving on a business trip-will get back to you later this PM or tomorrow.
    Thanks for your help.

  10. #10
    New Lounger
    Join Date
    Nov 2001
    Location
    The Woodlands, Texas, USA
    Posts
    16
    Thanks
    1
    Thanked 5 Times in 1 Post

    Re: Empty Cell Reference In Another File (2000)

    Doesn't this use a lot of resources if you have a whole lot of cells to compute?
    Is it not possible to throw a switch somewhere to force all arithmetic that encounters an empty cell to produce a new empty cell as its result?

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty Cell Reference In Another File (2000)

    Yes, it does.

    The only way I can thing of to do what you are suggesting is to attach the following code (or similar) to a button on the target sheet - i.e. you can't do it with formulae.(To the best of my knowledge) Of course, if the workbook is closed then the code would need adapting.

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 30/11/2001 by Brooke
    '

    '
    Windows("June01_prodlist.xls").Activate
    ActiveWindow.Panes(1).Activate
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("hack_it_all_about.xls").Activate
    Range("A1").Select
    ActiveSheet.Paste
    End Sub

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

    Re: Empty Cell Reference In Another File (2000)

    The only other way that I can think of is to select Options from the Tools menu, click on the View tab and remove the check mark from next to "Zero values" in the "Window options" section. However, that will cause all cells that have a zero in them to display as blank.
    Legare Coleman

  13. #13
    New Lounger
    Join Date
    Nov 2001
    Location
    The Woodlands, Texas, USA
    Posts
    16
    Thanks
    1
    Thanked 5 Times in 1 Post

    Re: Empty Cell Reference In Another File (2000)

    Thanks very much. You folks are really neat!

Posting Permissions

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