Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    551
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste link gives zero (Excel xp)

    I have 2 sheets in excel. I copy a row on the source sheet and paste link it to the target sheet. Where there are blanks on the source sheet's rows, it give me zeros when i paste link it to the target sheet. I don't want zeros to show up. I can go to tools and uncheck zero values in the view menu but then i won't get the zeros that really should be there. Why does this happen? is there some sort of setting i can change to prevent this. thanks

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link gives zero (Excel xp)

    If there is some fixed relationship between the coordinates of the target cells on the target worksheet and those of the cource cells on the source worksheet, then you could try conditional formatting - such as turning the colour of the font to white. I suspect, however, that your layout may not be as simple as that.

    How much are you tied to using Paste Link? It is possible that some form of VBA function could be set up using a conditional ISNULL function, but that may turn out to be overkill.

    HTH
    Grüße

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,207
    Thanks
    14
    Thanked 331 Times in 324 Posts

    Re: Paste link gives zero (Excel xp)

    Instead of using PasteLink is equivalent to having something like:
    =Sheet1!A1
    copied across all the columns in the row.

    You could just enter:
    =IF(ISBLANK(Sheet1!A1),"",Sheet1!A1)

    and copy it across the row in all the columns. Real zeroes would be zeroes but blanks would be null.

    Steve

  5. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    551
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link gives zero (Excel xp)

    I was trying to get around using the IF statement. I have a source sheet that changes every month. My plan was to link my data sheet to the source sheet. When i get my new monthly source figures i would paste them to the source sheet and the "data" sheet would update. No all the numbers that come on the "source" sheet are necessary on the "data" sheet.

    The source sheet will have 5,000 rows and the data sheet make have about 500. I don't want to have to make an IF statement for all those rows unless there is an easy way to do it.

    Hope this makes sense. a sample is attached. Thank you for the help.

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

    Re: Paste link gives zero (Excel xp)

    We would need a little more information about data, and how the rows are selected for copy to the Data sheet. It looks like column A in the source sheet is a key or ID for the rest of the data in the row. However, it is not clear if there can be duplicate values in column A. The attached sheet shows one way to do it if there are no duplicate values in column A of the source sheet. The data sheet contains formula in cells B1:F1 that can be copied as far down the sheet as necessary. You can then copy values from column A of the source sheet and paste them into column A of the Data sheet, and columns B through F will fill in with the rest of the data from the row on the source sheet.
    Legare Coleman

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,207
    Thanks
    14
    Thanked 331 Times in 324 Posts

    Re: Paste link gives zero (Excel xp)

    If you are copying each month, why not copy- paste instead of copy - paste link? Then you won't have the problem.

    If you want the cells LIVE so source changes are automatically reflect changes in "copy" and still not have zeroes for blanks, some options I see:
    1)change blanks to null
    2)change blanks to =na() (better than (1) if you are going to plot the data
    3) keep it updated via some macro, so data is a "copy" and is updated when source is changed
    4) IF statements.

    Others might be available, if I understood why you need 2 copies of the data and how much "in-sync" they need to be.
    Steve

  8. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,869
    Thanks
    0
    Thanked 180 Times in 165 Posts

    Re: Paste link gives zero (Excel xp)

    Hi jha,

    You could achive the desired outcome by filling in the source sheet's empty cells with a tick mark. The following macro will do just that for the selected range:
    <pre>Sub FillinBlanks()
    Dim Cell As Range
    For Each Cell In Selection
    If Cell.Value = Empty Then
    Cell.Value = "'"
    End If
    Next Cell
    End Sub
    </pre>



    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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