Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    London, Ontario
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked cells display a zero (Excel 2003)

    I have a workbook with two worksheets. On Sheet2 I want to display the values of some cells on Sheet1, so I type the formula =Sheet1!A1. I now expand the formula by dragging into adjacent cells so that the values of contiguous cells on Sheet1 will appear.
    The problem is that some of the cells on Sheet1 are empty, so on Sheet2 they display as 0 (zero). This is the case even though the cell format is General, not Number (even Text has the same result).
    How do I get rid of the 0's from my cells without having to go in and delete them manually?

    Another question: If I expand Sheet1 by adding rows and/or columns, is there any way I can have an automatic, corresponding update occur on Sheet2? Or at least prevent overwriting of the cells on Sheet2?

    Roger Shuttleworth
    London, Canada

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

    Re: Linked cells display a zero (Excel 2003)

    You can use formulas like this:

    =IF(ISBLANK(Sheet1!A1),"",Sheet1!A1)

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

    Re: Linked cells display a zero (Excel 2003)

    <<Another question: If I expand Sheet1 by adding rows and/or columns, is there any way I can have an automatic, corresponding update occur on Sheet2? Or at least prevent overwriting of the cells on Sheet2?>>

    Yes, use this formula instead (starting from cell A1 on sheet2):

    =INDIRECT("'Sheet1!'" & CELL("Address",A1))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    New Lounger
    Join Date
    Jan 2004
    Location
    London, Ontario
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked cells display a zero (Excel 2003)

    Thanks to you, Jan, and Hans too.

    Both the answers you gave are good, but is there any way I can combine the formulas so that:

    The cell contents on Sheet2 automatically update if columns and rows are added to Sheet1, and
    Cells on Sheet2 that are linked to blank cells on Sheet1 are displayed as blank?

    Sorry if I'm asking for the moon! Your help is appreciated.

    Roger

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

    Re: Linked cells display a zero (Excel 2003)

    Like this?

    =IF(ISBLANK(INDIRECT("'Sheet1!'" & CELL("Address",A1))),"",INDIRECT("'Sheet1!'" & CELL("Address",A1)))

  6. #6
    New Lounger
    Join Date
    Jan 2004
    Location
    London, Ontario
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked cells display a zero (Excel 2003)

    Hello Hans

    Thankyou so much. This works fine, though I had to remove a few odd spaces that crept in during copy and paste.

    Roger

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked cells display a zero (Excel 2003)

    <hr>=INDIRECT("'Sheet1!'"&CELL("Address",A1))<hr >

    Just a question regarding this formula. I tried it this way, but received #REF. However, if I removed the single quotes (') around "Sheet1" then it worked. Is there are reason the single quote?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Linked cells display a zero (Excel 2003)

    The second single quote should be before the exclamation.

    The single quotes are not needed (but are not a problem) if the sheet name has no spaces. If the sheet name has any spaces the single quotes are required to mark the full name (excel will get "confused" by the space otherwise).

    Steve

Posting Permissions

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