How to make a list of "open" claims in separate worksheet (excel 2010)

[AleksBabic]

I have been trying for a long time now, and can not seem to come to a conclusion. This is what i have done so far:

=IF(DMRLOG!AC:AC="OPEN",DMRLOG!A:A,"")

excel.jpg

What i want is: i have a list of all my claims raised, when a claim is completed i will change the position to "Closed". i want another worksheet to tell me all the claims that are still open, so that i can see which ones still need to be dealt with.

So on the original worksheet where all the claims are logged (DMRLOG) if the row "AC" says "open" then i want the claim number (A) to be shown as a list in another worksheet.

At the moment with the formula i have used i get the list which is what i want, and it only shows the open claims, but there are blank rows where the closed claims would be if they where open.

Thank you in advance for all your help lets hope you understand what i meen, apologies for the rubbish explanation of what im trying to do.

Aleks

[sktneer]

Hi Aleks,

Welcome to the forum.

On OPEN DMRs tab, use the below array formula and drag down until you get the blank rows. Remember to confirm the formula with Ctrl+Shift+Enter

=IFERROR(INDEX('DMR LOG'!$A$2:$A$11,SMALL(IF('DMR LOG'!$AC$2:$AC$11="OPEN",ROW('DMR LOG'!$A$2:$A$11)-ROW('DMR LOG'!$A$2)+1),ROWS(A$2:A2))),"")

Please find the attached sheet to see if this is what you want.

[etaf]

=INDEX(DMRLOG!$A$2:$A$110,SMALL(IF((DMRLOG!$AC$2:$AC$110)="open",MATCH(ROW(DMRLOG!$A$2:$A$110),ROW(DMRLOG!$A$2:$A$110))),ROW(A1)))
use
control + shift + Enter
to use an array
and get {} around the formula

to handle errors you can use
=IFERROR(INDEX(Sheet1!$A$2:$A$110,SMALL(IF((Sheet1!$C$2:$C$110)="open",MATCH(ROW(Sheet1!$A$2:$A$110),ROW(Sheet1!$A$2:$A$110))),ROW(A1))),"")

[AleksBabic]

Hi Etaf and Sktneer,

I have tried both of your methods, and i cant seem to get it to work. I have attached the document i am working on, eventually the "DMR log" will be full of hundreds of claims. the claims at the moment in the worksheet are just made up to check if the formula works, before i *** all the information.

Could you try and have a look at what i am doing wrong. apologies if it is simple as chips.

Thanks

DMR Log 3 (NOT YET REAL CLAIMS).xlsx

[sktneer]

Please find the attached sheet.

[etaf]

so
=IFERROR(INDEX(DMRLOG!$A$2:$A$110,SMALL(IF((DMRLOG!$AC$2:$AC$110)="open",MATCH(ROW(DMRLOG!$A$2:$A$110),ROW(DMRLOG!$A$2:$A$110))),ROW(A1))),"")

using
Control + Shift + Enter
to get {} will work

then in B
=IFERROR(INDEX(DMRLOG!B:B,MATCH(A2,DMRLOG!A:A,0)),"")

not sure what date - DMR logged meant - so using Column B
just change
=IFERROR(INDEX(DMRLOG!B:B
B:B
to whatever column the date is in
then in column C
=IFERROR(TODAY()-B2,"")

see attached - modifications to your example sheet

[AleksBabic]

It is sorted! Brilliant thank you both for your help!

[sktneer]

Glad to help you. Thanks for the feedback.