Excel - Combine values from two sheets

[ensorg]

Hi,

I have two sheets in my excel workbook which pull data from external sources. Both sheets contain stock data. What I would like to is add the values on the two sheets together and display on the third sheet. Something along the lines of...

when partnumber in sheet1 is = partnumber in sheet2 add the two stock values together.

I can't for the life of me think of anyway to do this... anybody got any ideas?

Thanks

 

[danomaniac]

dim partnumber1, partnumber2, total1, total2, subtotal

partnumber1 = sheets("sheet1&quot.cells(2,1).value
total1 = sheets("sheet1&quot.cells(2,2).value
partnumber2 = sheets("sheet2&quot.cells(2,1).value
total2 = sheets("sheet2&quot.cells(2,2).value

if partnumber1 = partnumber2 then
subtotal = total1 + total2
sheets("sheet3&quot.cells(2,1).value = partnumber1
sheets("sheet3&quot.cells(2,2).value = subtotal
end if



I didn't actually try this or fine tune it, but it might get you going. Hope it helps..

 

[danomaniac]

If you don't want to use VBA, you can do it with formulas, too.

in the cell where you want the result put...

=IF(Sheet1!A4=Sheet2!A4,Sheet1!B4+Sheet2!B4,0)

This may be simpler...

 

[ensorg]

Cheers mate, I will have a look.

One quick question, with regards to the cell reference (2,1) what does this refer to? Is it A2 or B1 or maybe i am totally off track?

 

[danomaniac]

When you reference a cell using the cells command, it is Row, Column. Meaning cells(2,1) is row 2, column 1, or cell A2.

 

[ensorg]

Hi,

Thanks for the code you posted danomaniac, it got me started on the right track... I have posted below the code that I have written... it seems to do the trick... (PS it confuses the heck out of me and I wrote it LOL)

Private Sub CommandButton1_Click()

Dim partnumber1, partnumber2, subtotal, total1, total2, continue, continue2, cellref1, cellref2
cellref1 = 2
cellref2 = 2
Do Until continue1 = 1
partnumber1 = Sheets("Sheet1&quot.Cells(cellref1, 1).Value

Do Until continue2 = 1 Or cellref2 = 4000
partnumber2 = Sheets("Sheet2&quot.Cells(cellref2, 1).Value
If partnumber2 = partnumber1 Then
total1 = Sheets("Sheet1&quot.Cells(cellref1, 2).Value
total2 = Sheets("Sheet2&quot.Cells(cellref2, 2).Value
subtotal = total1 + total2
Sheets("Sheet3&quot.Cells(cellref1, 2).Value = subtotal
Sheets("Sheet3&quot.Cells(cellref1, 1).Value = partnumber1
continue2 = 1
Else
cellref2 = cellref2 + 1
End If
If cellref2 = 3999 Then
total1 = Sheets("Sheet1&quot.Cells(cellref1, 2).Value
Sheets("Sheet3&quot.Cells(cellref1, 2).Value = total1
Sheets("Sheet3&quot.Cells(cellref1, 1).Value = partnumber1
End If
Loop
If partnumber1 = "" Then
continue1 = 1
Else
cellref1 = cellref1 + 1
cellref2 = 2
continue2 = 0
End If
Loop

End Sub

 

[danomaniac]

Excellent. Glad to help.