Creating Summary sheet for multiple cells & worksheets

[ChrisBurns]

Hi all,

I have a number of individual worksheets all in the same workbook that show current Projects and relevant Programme Managers depending on the Project Manager assigned to run the Project
Rather than have a changing lookup via drop down etc I have been asked to have these sheets as individuals with 'next sheet' 'previous sheet' buttons to navigate forward/backward.

On each sheet there is a check box to indicate if there is a requirement to change the Project Managers line manager......
this all works fine......

I have now been asked if I can create a summary sheet on the basis that
if the check box is true - compile the data

there are unused cells in these Project manager sheets and only need to pull cells where data exists

I am using an array to pull the data into these sheets where Data is the raw information

{=IF(ISERROR(INDEX(Data!$A$2:$Z$99,SMALL(IF(Data!$A$2:$A$99=$C$6,ROW(Data!$A$2:$A$99)),ROW(1:1))-1,3)),"",INDEX(Data!$A$2:$Z$99,SMALL(IF(Data!$A$2:$A$99=$C$6,ROW(Data!$A$2:$A$99)),ROW(1:1))-1,3))}

any ideas are really appreciated

Thanks