Reference number

[Bklynhyc]

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.

 

[Tom Hutchins]

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

 

[Glenn]

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

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))

 

[Bklynhyc]

Hi,

Thank I tried it out, and it worked, But when I input the formula into my
file it gave a #VALUE!. Does having more columns is the problem?

Thank again

 

[Bklynhyc]

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

 

[Glenn]

Not to me! But I'm not sure what you are really asking...

 

[Bklynhyc]

does it matter if the first sheet with the refer. the dates are 09/01/09 and
the second that needs refer. the dates are 09/01/2009?

 

[Tom Hutchins]

I used the same column references that you used in your example. I suspect
the problem is that one or both sets of dates are formatted as text, not as
real Excel dates. If they are real dates, the underlying date value would be
the same whether the date is displayed as 07/02/09 or 07/02/2009 or
02-Jul-09, etc. If the dates are text, then 07/02/09 is not the same as
07/02/2009. I suggest you convert the dates all to real Excel dates. Select
the dates on the 'Deposits And Credits' sheet, then select Data >> Text to
Columns >> Delimited >> Next >> Next >> Date >> Finish. Repeat with the dates
on the other sheet.

Hope this helps,

Hutch

 

[Bklynhyc]

I used the same column references that you used in your example. I suspect
the problem is that one or both sets of dates are formatted as text, not as
real Excel dates. If they are real dates, the underlying date value would be
the same whether the date is displayed as 07/02/09 or 07/02/2009 or
02-Jul-09, etc. If the dates are text, then 07/02/09 is not the same as
07/02/2009. I suggest you convert the dates all to real Excel dates. Select
the dates on the 'Deposits And Credits' sheet, then select Data >> Text to
Columns >> Delimited >> Next >> Next >> Date >> Finish. Repeat with the dates
on the other sheet.

Hope this helps,

Hutch