Consolidating Reults in Summary Sheet

[shyam sundar]

Hi All,

I am having a date wise consolidated file, in which I would like to add the ticket number status(Open, closed, N/A) in the Summary Sheet.
In this report once the ticket were closed it will automatically get dropped from the report.

I want to check the below conditions and update the summary sheet as Open, closed & N/A.

In the sample file, I have given four sample dates

1) In the summary sheet, we should check all the unique ticket numbers in the remining sheets and update it in the first column.
2) On 12th Apr sheet, you could see only two tickets are available. Hence, in the remaining tickets I have updated it as "N/A"
3) On 13th Apr sheet, you could see two new entries and one ticket got closed. Hence for the 1 ticket status updated as closed and for the remaining three tickets status updated as Open.

Similary for the other sheets as well. Is there any workaround for this.

[JohnTopley]

Why a sheet per day rather than one sheet with all days/dates/status?

Not good practice as consolidating from multiple sheets is much more difficult than breaking out data from a single source!

[shyam sundar]

Each date sheet consist of one day's report. I do understand its not a good practice, however for this report alone we maitain this way.. Since the closed records are getting dropped.

[Glenn Kennedy]

B2, copied across and down.

=IF(AND(C2="Closed",ISERROR(MATCH($A2,INDIRECT("'"&TEXT(B$1,"dd mmm")&"'!A:A"),0))),"Closed",

IF(AND(C2="Open",ISERROR(MATCH($A2,INDIRECT("'"&TEXT(B$1,"dd mmm")&"'!A:A"),0))),"Closed",

IF(ISNUMBER(IFERROR(MATCH($A2,INDIRECT("'"&TEXT(B$1,"dd mmm")&"'!A:A"),0),"")),"Open","N/A")))