Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    It Just Doesn't Add Up (XL97;SR2)

    I have two workbooks each containing data in the same data structure ie Jan to Dec and formulas. The workbooks are identical other than the data contained in each one. I thought it would be quite simple to select/copy the data range from WB01 and pastespecial the values to WB02 using the operation ADD feature. What I find happening is that XL is also adding the value of the formulas in WB01 to the formula in WB02.

    Any suggestions would be appreciated.
    John

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It Just Doesn't Add Up (XL97;SR2)

    Excel seems to be doing what is being asked of it.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It Just Doesn't Add Up (XL97;SR2)

    I agree with you but doesn't make sense to add a value to a formula. I've tried a two step approach ie copy/pastespecial value and then just copy/pastespecial formual thinking it would only copy the formula over since the range of data is the same. Unfortunately it pastes the values of WB01 as well even though the value is not selected to be copied over again.

    John

  4. #4
    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: It Just Doesn't Add Up (XL97;SR2)

    What exactly are you trying to do?
    When you PasteSpecial - Add excel adds the contents you copied from to what is in the cell. You can add it as a value or a formula. If you do NOT want to ADD it, just keep none selected and it will overwrite it with the new formula.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It Just Doesn't Add Up (XL97;SR2)

    Steve,

    I'm trying to add the values of WB01 to WB02 and preserve the formulas in WB02 (although the formulas are the same in WB01). It appears XL is not cooperating. One would think that if you kept the none selected it would only overwrite it with the new formula.


    John

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: It Just Doesn't Add Up (XL97;SR2)

    John, PasteSpecial doesn't do what you want. However, by just recording the formula via macro recorder and doing a little (or a lot if it's complex) editing, it's easy to write a macro to do it, as in this simple example.

    Sub Macro1()
    Dim rngCell As Range
    For Each rngCell In Selection
    rngCell.FormulaR1C1 = "=SUM(RC[1]:RC[5])+SUM(Sheet2!RC[1]:RC[5])"
    Next rngCell
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    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: It Just Doesn't Add Up (XL97;SR2)

    When I copy formulas from sheet2 and paste special values with ADD to Sheet 1 I get the formula from sheet1 + the value from sheet 2

    If you paste special formulas from sheet2 with ADD I get the the 2 formulas added.

    I am not sure what excel is not doing for you.

    What does it give you when you paste special values with ADD?

    Steve

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It Just Doesn't Add Up (XL97;SR2)

    Steve,

    Please note the cell formula in D6 =(SUM(D15))+15.

    The values were added correctly in cells D15 but the value of B6 was added to the formula in D6. I used copy/PasteSpecial Values + Add

    John

  9. #9
    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: It Just Doesn't Add Up (XL97;SR2)

    I guess I am obtuse, I am still confused:
    What cells in your example are you copying from (and what are the formulas)?
    Where are you copying to, and what formulas/values are in there before?
    What do you "expect" to be in there when you are done?

    If I understand correctly, Your example seems to show exactly what I would expect to happen. You are adding the values to the formulas. Are you trying NOT to change the formula in D6? You seem to want to NOT add to this, if so don't paste over this cell!
    Steve

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: It Just Doesn't Add Up (XL97;SR2)

    What you need to use is Copy, PasteSpecial, Formulas, Add. However, that will duplicate the formula without providing references to the other Workbook and Worksheet, which is the point I did not articulate clearly above.

    Say that you have =SUM(D15) in cell D6 in "SourceBook.xls" in "Sheet-S" and you have =SUM(D15) in cell D6 in "TargetBook.xls" in "Sheet-T"

    If you copy cell D6 in "SourceBook" in "Sheet-S" and then select D6 in "TargetBook" in "Sheet-T", then select Edit, PasteSpecial, Formulas, Add you will get =(SUM(D15))+(SUM(D15)). The sheet references are lost.

    What I assume you need in TargetBook Sheet-T is: =SUM(D15))+(SUM([SourceBook]Sheet-S!D15)

    Excel doesn't do that.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It Just Doesn't Add Up (XL97;SR2)

    Steve,

    I've attached a sample file. Please look at the two sheets named Step 1 and Step 2 respectively. I have inserted text boxes in each sheet to depict the flow of what I'm trying to do.

    Step 1 - Copy/Paste B1:B6 to D1
    Step 2 - Copy/PasteSpecial VALUES using ADD A1:A6 to D1

    The result of D6 should be 35 not 50

    Hope this helps. Sorry that this is turning into one of those long threads.

    John

  12. #12
    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: It Just Doesn't Add Up (XL97;SR2)

    I think he wants to add the items in the OTHER cells but his SUMMATION formula (and any formula) in his paste range gets "whammied", since you end up doing the original formula AND adds the VALUE from the copy.

    I think he wanted to paste from one sheet to another to get a summation of both, but using ADD, screws up the formulas, and using "NONE" overwrites the values.
    I think he wants to PASTE ONLY the values when it does NOT have a formula. He wants the formulas to remain unchanged.

    If my supposition is correct, this macro should do what he wants. This looks at every cell in Sheet1's used range and will add the value from sheet1 to the same cell in sheet2 PROVIDED THAT, Neither has a formula, BOTH are numeric, AND the cell in Sheet is NOT blank. Change the sheet names as appropriate and include a defined range if desired.

    Steve

    <pre>Option Explicit
    Sub ThisAddsUp()
    Dim rng As Range
    Dim rCell As Range
    Dim rCellTo As Range
    Dim wks As Worksheet
    Dim wksTo As Worksheet

    Set wks = Worksheets("Sheet1") 'Change as appropriate
    Set wksTo = Worksheets("Sheet2") 'Change as appropriate
    Set rng = wks.UsedRange 'or use eg Set rng = wks.Range("A25200")

    For Each rCell In rng
    Set rCellTo = wksTo.Range(rCell.Address)
    If rCell.HasFormula = False _
    And rCellTo.HasFormula = False _
    And IsNumeric(rCell.Value) _
    And IsNumeric(rCellTo.Value) Then
    And rCell.Value <> "" Then

    rCellTo.Value = rCellTo.Value + rCell.Value

    End If
    Next rCell
    End Sub
    </pre>


    I am only speculating since I am not sure exactly what he wants to do.

    Steve

  13. #13
    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: It Just Doesn't Add Up (XL97;SR2)

    I responded to JohnBF before I saw your note or file. I think my speculation is correct.
    Explanation. When you copy-Pastespecial Value - Add from A1:A6 to D16 You get:
    The CURRENT D1 value + the VALUE from A1 (=2 + 1) and since D1 is a value it sums them for you (=3) in D1
    It does the same for D25

    Since D6 has a formuls, it takes the current formula and ADDS the VALUE from A6 (this is what you told it to do) so you get :
    Since A6 has a formula it calculates it the =(SUM(A1:A5)) and uses the value of "15" to give you the FORMULA in D6
    =(SUM(D15))+"B6- value"
    =(SUM(D15))+15

    If you would have Paste-special FORMULA - add you should get:
    =(SUM(D15))+(SUM(A1:A5))

    Since you seem to want to add VALUES, but LEAVE formulas the macro I have in my last response might be applicable. You can NOT do what you want directly. You either must ADD to all the cells or copy over ALL the cells in the range.

    Steve

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: It Just Doesn't Add Up (XL97;SR2)

    Steve,

    The code you provided does exactly what I want. I have reworked my "Test" file and attached it for those of you who are interested.

    John

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: It Just Doesn't Add Up (XL97;SR2)

    I believe Lotus 123's paste ADD operation does exactly what you want.
    Cells are not 'added' if the destination cell contains a formula.
    Viv la difference?

    zeddy

Posting Permissions

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