Results 1 to 15 of 15

20030617, 17:53 #1
 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

20030617, 18:24 #2
 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.

20030617, 18:31 #3
 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

20030617, 19:08 #4
 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

20030617, 19:36 #5
 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

20030617, 20:55 #6
 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 SubJohn ... I float in liquid gardens
UTC 7ąDS

20030617, 22:38 #7
 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

20030618, 14:27 #8
 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

20030618, 15:32 #9
 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

20030618, 16:00 #10
 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 "SheetS" and you have =SUM(D15) in cell D6 in "TargetBook.xls" in "SheetT"
If you copy cell D6 in "SourceBook" in "SheetS" and then select D6 in "TargetBook" in "SheetT", 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 SheetT is: =SUM(D15))+(SUM([SourceBook]SheetS!D15)
Excel doesn't do that.John ... I float in liquid gardens
UTC 7ąDS

20030618, 16:19 #11
 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

20030618, 16:26 #12
 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

20030618, 16:40 #13
 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 copyPastespecial 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 Pastespecial 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

20030618, 21:10 #14
 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

20030619, 21:40 #15
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,442
 Thanks
 166
 Thanked 651 Times in 619 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