Log In
Register
Home
New Member
October 20, 2020
Offline
Hi,
I am currently working on improving an audit tool template. The data comes to me in the form of PDF or Word documents from various branches in an organisation.
The audits are split into sections with subsections that require a Yes, No or N/A option. Currently this is input into Excel and I am then able to determine the compliance % per subsection and section per branch, and the groups compliance per subsection and section and total overall compliance.
I would like to make this more interactive and report more information such as which audits are completed by a member of staff and their compliance score etc.
I believe that this can all be done but I am struggling with the best way to lay out the initial information to produce something which could be used to create a dashboard. I am unable to upload the file as there is a macro which seems to be stopping it.
Any help would be appreciated
July 16, 2010
Offline
Hi Guy,
Welcome to our forum! It's difficult to give specific advice without seeing the file, however the number one rule of data layout is a tabular format. From there you can easily analyse and summarise the data with PivotTables and formulas.
Also, you might find it helpful to know that with Microsoft 365 you can get data from a PDF and load it into Excel automatically.
I hope that helps.
Mynda
New Member
October 20, 2020
Offline
Thanks Mynda,
I have removed the Macros in the file and uploaded (the macros only ran the file name from a specific cell so nothing really that effects the data) but think I need to input the data as you have suggested and work from that.
Thank you for the tip regarding data from a PDF.
Many thanks
Guy
July 16, 2010
Offline
Hi Guy,
The layout of your workbook suits data entry, but it will be a nightmare to use it in reporting. You can use Power Query to consolidate the data from the various sheets, but you'll be best to first fix the structure of the sheets to tabular if you can, as this will make the consolidation easier.
The nested IF's in column B of each sheet aren't ideal. If you have Office 365/Microsoft 365 I'd fix the layout of the Criteria sheet data to a Tabular format and then use the FILTER function to return the list of criteria for each audit section.
I hope that gives you some ideas.
Mynda
1 Guest(s)
