Managing Multiple Audit Sheets

tmayhall

I'm working on setting up a management system for process audits, which are required in various departments on either a monthly or quarterly basis.  Each sheet contains several rows of audit criteria and yields a single score on the top parent row showing plant, audit, date, and score.  Since there are multiple plants, multiple audits, and frequent audits, the number of these will quickly get quite large.  I'd like to have a combination sheet or report that pulls in only the parent row from each audit, but I'd also like to be able to include formulas so that I could tell whether the audits are completed on time (requiring a rolling due date depending on prescribed frequency), and highlights audits that are below standard.  Where should I go for tips on handling multiple sheets in a format that allows formulas (I don't think these are permitted in Reports)?

Andrée Starå

Hi,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

Hi,

To collect the information on a so-called master sheet, you could use cell-linking or cross-sheet linking, and it also has to be on a sheet because you are correct that it's not possible to use formulas in reports.

Hope that helps!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

tmayhall

Thanks, Andree! Samples of individual audit submission and summary report are attached.

Each audit is formatted for ease of entry via phone or iPad on the floor.  The individual items are scored to yield a single score by area by plant.  Only this single score is needed for the summary, plus area identifying information.  For overall management, I need see only the latest audit for each area in each plant (so filtered by plant by audit and by maximum date), and to highlight any past-due audits (by comparing the last audit date to the next due date) and to highlight any sub-standard (compared to target values).

I think the first step is the trickiest.  Smartsheet is set up well to sort through and select from a list of tasks within a hierarchy.  However, in this case, I need a kind of 90 degree pivot first, since the individual audit items are not sub-tasks in the traditional project management scenario.  If I can filter out only the parent rows from each of the submitted audits, I think it will be relatively smooth to write formulas for date selection and performance to target and write reports and create dashboards from there.  It's just using a master sheet to list only the parent row that's stumping me.

Rosie.bradbury85341

Dear T Mayhall

Could I perhaps borrow abit of your time to talk about smartsheet audit templates. I've no budget for support so finding my way in the dark sadly.

Rosie

Rosie.bradbury85341

Tan

Any chance I could utilize your expertise for 30 mins.

I'm based in UK

My thanks in advance rosie

Andrée Starå

Dear Rosie,

Sorry to hear that you didn't get any budget for such an important process. I tried!

I'd be happy to help again in any way I can!