Link cells on 2 different sheets reciprocal

[martho]

Hello!

I have 2 sheets in a document like:

Sheet 1: Invoices
1 Invoice 123 from company abc
2 Invoice 987 from company xyz
3 Invoice 222 from company ccc
...

Sheet 2: Payments
1 Payment 100 EUR
2 Payment 200 EUR
...

I need to assign the payments to the invoices. This has to be done manually. But you should be able see automatically if a payment has been assigned to an invoice and vice versa. It's always a 1-to-1 link in my case.

To link Payment 2 to Invoice 3, you could of course do something like:
Sheet1.D3 = Sheet2.A2
and
Sheet2.D2 = Sheet1.A3
... but you have to make 2 steps which are redundant and might lead to errors. Is there a way to do this in 1 step? A problem might be that late invoices and payments can be inserted so it's no option to work with row-numbers as cell-values because they might change.

[ken johnson]

SUMPRODUCT function tests multiple criteria to determine payment.
With Invoice number in column A, Company name in column B on both sheets and Payment in column C on the second sheet then...