Array Excel Formula : Need Help

[blackorchids2002]

Hi Masters,

Please help me how to get the correct array formula to display the unpaid invoice from "Payable Details" sheet into a "summary" sheet.

Here are the data from PAYABLE DETAILS and their specific columns in my file.

[TABLE="width: 483"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column AH[/TD]
[/TR]
[TR]
[TD]Code[/TD]
[TD]INVOICE DATE[/TD]
[TD]DUE DATE[/TD]
[TD]Invoice#[/TD]
[TD]PAYMENT STATUS[/TD]
[/TR]
[TR]
[TD]XPRD[/TD]
[TD]28-May-15[/TD]
[TD]27-Jun-15[/TD]
[TD]49590042[/TD]
[TD]UNPAID[/TD]
[/TR]
[TR]
[TD]XPRC[/TD]
[TD]30-May-15[/TD]
[TD]29-Jun-15[/TD]
[TD]49611824[/TD]
[TD]FULLY PAID[/TD]
[/TR]
[TR]
[TD]XPRD[/TD]
[TD]29-May-15[/TD]
[TD]28-Jun-15[/TD]
[TD]49615053[/TD]
[TD]UNPAID[/TD]
[/TR]
[TR]
[TD]XPRD[/TD]
[TD]5-Jul-15[/TD]
[TD]4-Aug-15[/TD]
[TD]49632691[/TD]
[TD]UNPAID
[/TD]
[/TR]
</tbody>[/TABLE]

The output wold be like this in the summary sheet. My formula did not work to display like the output below.

[TABLE="width: 366"]
<tbody>[TR]
[TD]Invoice Number [/TD]
[TD]INVOICE DATE[/TD]
[TD]TOTAL INVOICE[/TD]
[/TR]
[TR]
[TD]49590042[/TD]
[TD]28-May-15[/TD]
[TD]$199[/TD]
[/TR]
[TR]
[TD]49615053[/TD]
[TD]29-May-15[/TD]
[TD]$250[/TD]
[/TR]
[TR]
[TD]49632691[/TD]
[TD]5-Jul-15[/TD]
[TD]$175[/TD]
[/TR]
</tbody>[/TABLE]


This is the formula I used.

=IFERROR(INDEX('PAYABLE DETAILS'!$D$1:$D$1000,SMALL(IF('PAYABLE DETAILS'!$AH$1:$AH$1000="UNPAID",ROW('PAYABLE DETAILS'!$AH$1:$AH$1000)-ROW('PAYABLE DETAILS'!$A$1)+1),ROW($Z$1:Z1))),"")


Thanks,

 

[Marcelo Branco]

Assuming
1. data in sheet PAYABLE DETAILS begin in row 2
2. you are inserting the first formula in cell C2 of Summary sheet

Adjust the ranges as the formula below and use ROWS($C$2:$C2) instead of ROW($Z$1:Z1)

=IFERROR(INDEX('PAYABLE DETAILS'!$D$2:$D$1000,SMALL(IF('PAYABLE DETAILS'!$AH$2:$AH$1000="UNPAID",ROW('PAYABLE DETAILS'!$AH$2:$AH$1000)-ROW('PAYABLE DETAILS'!$AH$2)+1),ROWS($C$2:$C2))),"")

Ctrl+Shift+Enter

Hope this helps

M.

 

[MUKESHY12390]

follow below step.....

=IFERROR(INDEX('Payable Details'!A$2:A$1000,SMALL(IF('Payable Details'!$E$2:$E$1000="UNPAID",ROW('Payable Details'!$E$2:$E$1000)-ROW('Payable Details'!$E$2)+1),ROWS('Payable Details'!$E$2:$E2))),"")

copy formula and paste it to sheets---> summary sheet---->A2----F2(Function F2 key---for edit)--Ctrl+shift+Enter
select A2:E2------->Ctrl+R
select A2:E100----->Ctrl+D