ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Reference number (https://www.excelbanter.com/excel-worksheet-functions/247087-reference-number.html)

Bklynhyc
Reference number
 
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
Reference number
 
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.

Glenn
Reference number
 
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))

Bklynhyc
Reference number
 
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.

Bklynhyc
Reference number
 
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))
.

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


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

"Glenn" wrote:


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

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.

Bklynhyc
Reference number
 
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?

"Bklynhyc" wrote:

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

Tom Hutchins
Reference number
 
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.

Bklynhyc
Reference number
 
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.


All times are GMT +1. The time now is 08:29 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com