0

I have an excel file prepared for summary/report. This will be used by one user, an administrator (not me).

There are some check-"cells" (Marlett checkboxes) that should be ticked before the summary report is ready to be submitted. This is not by any means obligatory, but it serves as reminder that the individual sheet (project) is not yet checked (thus may contain outdated information).

I've colored them red, and they will turn green when ticked (or filled). All required, and sensitive to false/outdated information is red. So, at a glance she will know which information need to be updated (if there's anything red).

There are three categories that need checking:

  1. External Documents (the first three in column A)
  2. Internal Documents (scanned documents, marked with "files" above red box)
  3. Check confirmations (confirm the price is right, the item is indeed non-taxable, and such)

I put 2 and 3 above the table, above the related groups. For example, "price check confirmation box" is above the column for prices, and "cost check confirmation box" is above the column for costs. However, this cause the checkboxes scattered across the worksheet, and hard to maintain/glance.

I feel my reason is justified to scatter the checkboxes, but I feel it's a poor design choice, as the administrator usually ignore the checkboxes, feeling it's a hassle to check them at all.

How can I improve the double-check list to make it easier to find and check?

Screenshot

Note: I can post additional screenshot or upload the excel file if needed.

1

What I would do is have a checklist at the top, on the right of the spreadsheet, where the fields contained the word "confirmed" or "empty" and conditionally format them red or green depending on whether the required fields in the spreadsheet are completed.

Please note my solution relies on the field to be checked having an initial value of 0.

This means that all the confirmation fields are in the same place, and they get updated automatically when the administrator fills in the details in the spreadsheet.

I have created an example in Google spreadsheets for you. The formulae should work in Excel as well.

formula and conditional formatting

  • 1
    Thank you. Grouping them up seems to be the most logical to be able to know everything at a glance. Thank you! – Vylix Sep 29 '17 at 15:02
  • Glad it helped :) Automating the confirmation so that the administrator doesn't have to do it seems like the way forward, too – Yvonne Aburrow Sep 29 '17 at 15:04

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.