Advanced Excel Tool Creation Help - Gap Analysis style

**Diamond Dog** · 09-19-2012, 08:43 AM

Hi all,

I'm new to the forum and this is my first post, so selfishly it's me wanting some help! I've just started a new job and have been asked to create an advanced excel tool that will do some funky stuff to help my manager analyse gaps in compliancy.
So here are my requirements

I have a list of clauses/frameworks that the company must comply with, so in my spreadsheet i currently have a a numbered column and a list of frameworks in another column.
The third column has the header 'Compliant?'. The user of the tool can then click a drop down box and choose, yes, no or partially (This is yet to be implemented)
The Final column is simply a comments column.

This seems pretty straight forward, but this is where I need help. These frameworks are what the company must comply with to meet the requirements of third party companies, however every third party requries us to comply with different frameworks.
These frameworks may be specific to that company or generic, e.g. data protection act.

How would I go about creating a tool that allowed the user to choose the company name (via filter or dropdown) and display the the frameworks thats we must comply with and whether we comply (via user input) or not?
Is it possible to just list every single framework and then filter them based on which company is chosen...but how would that relate to the comply column? Since one frameowrk may be common for all third parties but we may not comply with that framework for each company.
e.g we may comply with framework X for company A but not for Company B and that must be reflected when the user changes the filter.

If this is not possible, would I need to write out each framework for each company (including duplicates) and filter them that way?
Sorry if I haven't explained this well, but I hope you get the idea. I didn't want somebody to do this for me, I just wanted some suggestions on how I should go about tackling this and what methods to use. Since I'm very new to advanced excel (Though I have a programming and computer science background)

Thanks guys!

**K m** · 09-19-2012, 08:50 AM

How about posting sample data?

**Diamond Dog** · 09-19-2012, 09:24 AM

Ermm not sure how to post sample data, or if i am allowed to, but does this help..

sample data.JPG

As you can see, our company must comply with the compliance, privacy and security acts for both Acme co and Company X. But whether we comply or not for each company is different.
I would like to be able to click a dropdown and choose the company, display the framweworks relevant for that company and list our compliance state, for that framework, specific that company (Which can be changed for each company).
I hope that makes sense

**Cutter** · 09-19-2012, 11:49 AM

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**Diamond Dog** · 09-19-2012, 12:00 PM

Hi,

Thnaks for that. Attached is the workbook I showed a screenshot of. Its not much as I just wanted a general Idea of what direction to go in implementing this and I am not allowed to post the data I already have.
If someone wanted to take a look at the sample data and maybe show a way of it working.