did that as well, would it matter if the beginning ref. number start at 140?

"Glenn" wrote:

Tom Hutchins wrote:
Here is one solution that uses SUMPRODUCT to test the date and amount and
return the row number:

=INDIRECT(ADDRESS(SUMPRODUCT(--('Deposits And
Credits'!$A$2:$A$500=B2),--('Deposits And
Credits'!$D$2:$D$500=C2),ROW('Deposits And
Credits'!$E$2:$E$500)),5,,,"Deposits And Credits"))

Hope this helps,

Hutch

"Bklynhyc" wrote:

I would like to see if i can use one spread sheets reference number to
display on another, using two information. The DATE and AMOUNT.

it looks like this;

sheet one with refer. no. - title (Deposits And Credits)

Date Type Description Amount Ref.
07/02/09 Deposit DEPOSIT $4,873.13 4
07/02/09 Deposit DEPOSIT $113,198.08 5

second sheet without refer. no.

Transaction Description Date Amount Ref.
DEPOSIT 7/2/2009 113,198.08 ?
DEPOSIT 7/2/2009 4,873.13 ?

I've put the formula in "?" like this;
=INDEX('Deposits And Credits'!$E$2:$E$500,MATCH(1,('Deposits And
Credits'!$A$2:$A$500=B2)*('Deposits And Credits'!$D$2:$D$500=C2),0))

The answer that I've been getting is N/A. note there are about 200 lines
like this.

Please help, Thank You.



Array formula (commit with CTRL+SHIFT+ENTER):

=INDEX('Deposits And Credits'!$E$2:$E$5005,
MATCH(B2&C2,'Deposits And Credits'!$A$2:$A$500&
'Deposits And Credits'!$D$2:$D$500,0))
.