Fetch All related records of specific cutomer....

[S. Obaid]

Hi,

Any possibility to show all related record of a
customer on a separate sheet just by typing
the name/code of that customer...??? Given is
the situation to help you understand scenario...

SHEET - 1 (starting from A2)
=========
Customer Receivable($) Inv.Date(dd/mm) Aging
Company-A 600 05/07 2
Company-B 350 02/07 5
Company-C 500 25/06 13
Company-A 200 22/06 15
Company-C 100 11/06 26
Company-A 400 01/06 36
Company-A 300 05/05 63
Company-A 300 04/05 64

Now on a separate Sheet2, If I type name of customer
"Company-A" in cell [SHEET2!A1] , All the related records
of Company-A is displayed in this sheet (Sheet2)

SHEET - 2 (In cell A1 I typed Company-A)
========
A B C
D
1 "Comapny-A
2 Customer Receivable($) Inv.Date(dd/mm) Aging
3 Company-A 600 05/07 2
4 Company-A 200 22/06 15
5 Company-A 400 01/06 36
6 Company-A 300 05/05 63
7 Company-A 300 04/05 64

Any way.....?? I want to use this as reporting purpose...

Syed

 

[Frank Kabel]

Hi
possible. try the following array formulas (entered with
CTRL+SHIFT+ENTER) on your second sheet:

A3:
=INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$A$1:$A$100=$A$1,ROW('shee
t1'!$A$1:$A$100)),ROW(1:1)))

B3:
=INDEX('sheet1'$B$1:$B$100,SMALL(IF('sheet1'!$A$1:$A$100=$A$1,ROW('shee
t1'!$A$1:$A$100)),ROW(1:1)))

C3:
=INDEX('sheet1'$C$1:$C$100,SMALL(IF('sheet1'!$A$1:$A$100=$A$1,ROW('shee
t1'!$A$1:$A$100)),ROW(1:1)))
.....

Copy this formulas down. If you also want to prevent the error messages
if no valid entry exist anymore try
A3:
=IF(ISERROR(INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$A$1:$A$100=$A$
1,ROW('sheet1'!$A$1:$A$100)),ROW(1:1)))),"",INDEX('sheet1'$A$1:$A$100,S
MALL(IF('sheet1'!$A$1:$A$100=$A$1,ROW('sheet1'!$A$1:$A$100)),ROW(1:1)))
)


But in addition you may consider using 'Data - Filter - advanced
Filter' or a standard Autofilter to create extracts of your list.