Consolidating data from multiple worksheets for status report

Hello,
My firm has a status report of accounts we are reconciling for different divisions. Data for each division is in a different worksheet in the following format.

Screen Shot 2015-03-08 at 3.08.01 pm.png



Is there anyway that a summary sheet can be added into the status report - one that provides a snapshot of what accounts are outstanding so that we don't have to individually go through every sheet for each division?


I have been thinking about consolidated pivot table but not idea how this works with text.
Ideally, would want to see a single table with the divisions listed and the accounts that have not been done. Another table will have all items that are done but not been reviewed.

Not everyone can see .png files on this forum - post a sample Excel workbook instead.

Pete

Sorry about that.

As suggested by Pete, have attached example Excel file.

The attached file shows how you can do this using a few formulae.

In the two Division sheets I've used column F as a helper column, with this formula in F2:

=IF(B2="N",MAX(F$1:F1)+1,"-")

I've copied this down to row 8, but in your real file you should ensure that the formula is copied down at least as far as your data, or further if you want to account for new data being added (the hyphens help to show where the formula is active). This formula sets up a unique sequential number for each record which matches the criteria (i.e. that column B is set to N). Cell F1 is the starting point for the numbering on each sheet. On the Division 1 sheet this is set to zero, but in Division 2 this contains the formula:

=MAX('Division 1'!F:F)

i.e. it looks back to the previous sheet to find the maximum used so far, and that is the starting point for the numbers in the second sheet. If you had a third sheet you would set cell F1 to look back at the Division 2 sheet, and so on. Consequently, the sequential numbering runs from one sheet to the next.

I've inserted a new sheet called combined, which has the same structure as the Division sheets in columns C to G - I've used columns A and B as helper columns to identify the sheet name where a record can be found and the row in that sheet where it occurs. To assist in this, I have set up a small table in columns K and L which lists all the Division sheets and records the maximum number encountered in each sheet. If you have more Division sheets you should add them to the bottom of this table.

The formula in C2 is this:

=IF($B2="","",INDEX(INDIRECT("'"&$A2&"'!A:A"),$B2))

and this extracts data from column A of the sheet name given in A2 from the row given in B2. Similar formula appear in columns D to G to bring data from different columns of the sheet/row combination. The formulae in row 2 should be copied down for as far as you need them (I've copied to row 10).

Note that the helper columns are all coloured blue - you can hide these columns if you want the sheets to look alike (but copy the formulae down sufficiently far before doing so). The rest is all automatic.

Hope this helps.

Pete