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 the right direction, it would be much appreciated.

I'm trying to compare two cells on two different worksheets and adding 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 to NewSalesTotal.

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

Thanks in advance,
Christine

[Jim Thomlinson]

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.

--
HTH...

Jim Thomlinson


"weeshie73" wrote:

>
> Hi All,
>
> I'm new to this board and new to VBA. If you could point me in the
> right direction, it would be much appreciated.
>
> I'm trying to compare two cells on two different worksheets and adding
> 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 to
> NewSalesTotal.
>
> I'm good at cut/paste and modifying the code to my needs but am having
> problems finding something similar to this.
>
> Thanks in advance,
> Christine
>
>
> --
> weeshie73
> ------------------------------------------------------------------------
> weeshie73's Profile: http://www.excelforum.com/member.php...o&userid=24792
> View this thread: http://www.excelforum.com/showthread...hreadid=383548
>
>

[weeshie73]

Jim,

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

Thanks!
Christine

[Jim Thomlinson]

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

--
HTH...

Jim Thomlinson


"weeshie73" wrote:

>
> Jim,
>
> You have my concept correct. WS#2 Accounts would be considered a
> master list. No new sheet is needed.
>
> Thanks!
> Christine
>
>
> --
> weeshie73
> ------------------------------------------------------------------------
> weeshie73's Profile: http://www.excelforum.com/member.php...o&userid=24792
> View this thread: http://www.excelforum.com/showthread...hreadid=383548
>
>

[weeshie73]

Thanks so much! Worked beautifully!