Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Paste link, zero values (Excel xp)

    I am paste linking one sheet to another. A large area of cells like A1:Z5000. I paste link them all at once. They consist of numbers and letters but a lot of cells are blank. When i paste link to the other sheet every place that is blank is zero now. I then go to tools, options and click on zero values but then i lose all my zeros which i don't want. Some of the zeros are in the data itself and they become blanks also. Is there an alternative or am i doing something wrong? Thank you.

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

    Re: Paste link, zero values (Excel xp)

    When you paste a link, what you are actually doing is pasting formulas. For example, if you paste a link to cell A1 on Sheet1 into a cell on another sheet, what you get is:

    <pre>=Sheet1!A1
    </pre>


    If you have a formula that results in a null string, that will be displayed as zero. The only way around this that I know of is to do what you have done and turn on the option to not display zero values, or change the formula to look like this:

    <pre>=IF(Sheet1!A1="","",Sheet1!A1)
    </pre>


    If you do this, you could write a macro to change the formula.
    Legare Coleman

  3. #3
    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: Paste link, zero values (Excel xp)

    Another option other than the IF suggestion that I use sometimes, is to NOT have blank cells in the source but fill them with =NA() to give an error function. the #N/A errors carry through and they can be be "hidden" with cond formatting.

    It works for plotting since it keeps lines connected, but plays havoc with stat functions. In that case you could add TEXT to the blanks ("") which will keep calcs working okay but not work with charts.

    You couls also use the "camera" to take a "picture" of the data for viewing and have any other references point to the original source, not the linked copy"

    The method depends on what you want to do with the "linked data".

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Paste link, zero values (Excel xp)

    oh brother! i didn't know it would be such a problem. I have to use it for to populate tables, make charts, and perform calculations.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Paste link, zero values (Excel xp)

    Hi jha,

    Depending on what you're doing, you might find that PasteSpecial|Formulas gives you the results you need. This will paste copies of the actual formulae and values from your source, and blank cells will stay that way. The upside is that you don't have to put masses of =NA() in the source or =IF(...) in the target, both of which could enlarge and slow your workbook down dramatically. The downside is that changes to values or formulae in the source won't automatically be updated in the target.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    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: Paste link, zero values (Excel xp)

    I didn't mention this, but if you do have "masses" of =NA() in your source, they can be converted to VALUES using copy-paste-special values and they will no LONGER be a formula, but will be an "error value"

    Steve

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Paste link, zero values (Excel xp)

    well, i've decided to uses the option in tools to supress the zeros and then deal with the data by using legare's IF statement. Can you tell me how to deal with this multiple if statement...

    If there is a 0 on sheet1 i need to make it a #. If there is a hyphen i need to make it a zero. I still don't understand the multiple IF's in one formula.

    =IF(Sheet1!B17=0,"#",Sheet1!B17)

    =IF(Sheet1!B17=0,"#",Sheet1!E17)


    Then i have to make a formula that substracts 1 column, Like B17 from E17. It is no problem if there is a 10 in B17 and a 5 in E17. But what if there is a # in B17 and a 5 in E17 or vice versa. Thanks for the help.

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Paste link, zero values (Excel xp)

    Hi jha,
    I just knocked this up. Run it on the source sheet and your pasted links should come out looking fine without zero suppression. What the macro does is to place tick marks in all empty cells in the used range. If that's too large a range, you could change UsedRange to Selection.

    Cheers

    <pre>Sub ConvertEmpty()
    Dim CellContent As Variant
    For Each CellContent In ActiveSheet.UsedRange
    If CellContent.HasFormula = False Then ' leave cells with formulae alone
    If Len(CellContent) = 0 Then
    CellContent.FormulaR1C1 = "'"
    End If
    End If
    Next
    End Sub
    </pre>

    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Paste link, zero values (Excel xp)

    the creativity just tickles me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  10. #10
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link, zero values (Excel xp)

    hey Steve,

    How exactly do you hid cells that contain #NA using cond. formatting? I'm using the sum command to sum a set of data thats linked to another workbook. Often times some of the data values will return the #NA statement which is find, however, I still need the sum to calculate the cells that does have values.

    Thanks

  11. #11
    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: Paste link, zero values (Excel xp)

    I "hide" them by making the text the same color as the background. If you want to calculate with ranges containing #N/As then the easiest is to use IF and put in null strings which aren't included in calcs for count. Or you must make more complicated formulas which will ignore the N/A errors (many array formulas come to mind)

    Steve

  12. #12
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link, zero values (Excel xp)

    Thanks. I not very familiar with using array formulas, but I'll try one out (by using help menu) and let you know how its works out.

Posting Permissions

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