Comparing Two Cells on Two Different Worksheets

[weeshie73]

Hi All,

I'm new to this board and new to VBA. If you could point me in th
right direction, it would be much appreciated.

I'm trying to compare two cells on two different worksheets and addin
values into a column on one of the sheets.

Data is set up as follows:

WS #1 Invoices
A1: Acct# B1: ShipToCode C1: InvoiceAmt

WS#2 Accounts
A1: Acct# B1: ShipToCode C1: NewSalesTotal

I'm trying to match Acct#/ShipToCode and then add the InvoiceAmt t
NewSalesTotal.

I'm good at cut/paste and modifying the code to my needs but am havin
problems finding something similar to this.

Thanks in advance,
Christin

 

[Guest]

You can probably do this without resorting to code but we need a little more
information. Let me know if I am wrong anywehere here. On sheet 1 and 2 you
have Account codes and Ship to Codes. The idea is to match up the Codes and
create a sum of the matching items. Do the all of the Account Codes and ship
to codes exist on at least one of the sheets. That is to say could at least
one of the sheets be considered a master list of all Account codes and Ship
to codes or do we have to create a new sheet that will be a master list of
all codes. It makes a difference in terms of the final solution I would
recommend.

 

[weeshie73]

Jim,

You have my concept correct. WS#2 Accounts would be considered
master list. No new sheet is needed.

Thanks!
Christin

 

[Guest]

On sheet 2 (your master sheet) add this formula anywhere on Row 2.

=SUMPRODUCT((A2=Sheet1!$A$2:$A$65536)*(B2=Sheet1!$B$2:$B$65536)*Sheet1!$C$2:$C$65536)

This formula will find all instances on Sheet 1 where the Account code and
the Ship To Code match with the Account code and ship to Code of the line you
are on. It will sum the values in Column C of those matching records. If you
need more info on sumproduct check out this link.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[weeshie73]

Thanks so much! Worked beautifully