Adding row content from sheet to sheet

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

This proposed solution has only been applied to the 'Operations Department' and 'Accreditation' sections of the 'Final' sheet.
The formula for columns B16 is:

Formula:

Please Login or Register  to view this content.

Once the formula is entered it may be dragged down to B17 and, while B16:B17 are selected, the fill handle is dragged across to C17.
At this point you'll need to select cell C16 and simultaneously press the Ctrl and c keys,
Select cell I16 and simultaneously press the Ctrl and v keys,
Drag the fill handle down to I17 then over to K17.
Select the range B16:K17 and enter a new conditional formatting rule that sets the font of cells with a value of zero to white.
Select cell B16 copy the formula from the formula bar,
Select cell B19 and paste the formula into the formula bar,
Change the 'Ops' in the formula to 'Accreditation' then repeat the above steps of copying down and across,
Apply the conditional formatting by selecting cell B16, select 'Format Painter' from the 'Home' tab, select cells B19:K20
The above process would then be repeated for each department.
Let us know if you have any questions.

To prevent overwriting the 'Accreditation' header row, row 18 would need to be selected and a new row would need inserted. The formulas from row 17 could then be dragged down into the newly inserted row 18. I don't know of a way to do by formula and believe it would require VBA/macro(s), of which I know too little to help.