on reason or another i need to convert one of my report using below

"Tom Hutchins" wrote:

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" wrote:

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

"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.