Greetings all,

The audit form is the culmination of work over the last year with alot of help from a member of this forum

http://www.excelforum.com/excel-prog...execution.html

Since then the workbook works fantasticly well but as i did not write the code an i am no guru of coding im finding it very hard to even know where to start with what i need to change or update to add these last 2 functions to the workbook as it is.

The work book has 7 sheets marked 1 -7 the 8th sheet is called report.

on the report sheet there are some check boxes which i can use to consolidate 1 or several sections in to a single report.

Example: using file below select section 1 - 7 and hit consolidate.

this will search the 7 sheetsfor given functions, specifically looking for non-conformance and then reports this data back which i can then print off a report to issue my colegues.

This all works fine but i would like to add the following function if possible:

1) on the report page i have added under site performance (%) a box. i would like the code to calculate based on what section or sections have been consolidated the performance of said sections.

I have added a box top right of each section with the total number of clauses in each section ( section 4 for eaxmple has 115) so i imagine the code would need to add total number of section totals up, defined by the checked boxes and calculate % based on that total number subtracted by non conformences raised which would give a percentage.

2) the second function i would like to add is more complicated i think.

i created a sheet called Grading Table, this table defines what grade somebody would achieve if they achieved certain scoring within this table.
What i would like is that upon consolidation of data for the report, that a piece of code if possible would analyse the data reported ( minors = X majors = Y Criticals = Z) and use the table to identify and report the grade associated with the score on the report page below box titles grade.

I would like this to only be calculated if all 7 section check boxes are highlighted however, when less than 7 are selected i want the box to display N/A

3) the calculation for the above is based on 2 variables in the table, Announced audit or Unannounced audit.
so i would like a box on the report page called select which if selectd wil use data from announced grading or unchecked would be unannounced grading.

I Hope this makes sense and if anyone can help me with this or offer advice i would be very appreciative.

(please note there are some controls / instructions for this file, on the report page hit Help and it will show them.)


Internal Systems Audit Form.xlsm