Controls Matrix using Slicers

[Peak Seagull]

Hi,

I’m hoping someone can offer some words of wisdom on setting up a spreadsheet that uses slicers to select a # wording that will then display everything associated with that # wording.

As I am struggling to do this (and don’t even know if it’s possible) I’ve attached a Word document that shows the data I am trying to manipulate. As you can only enter one data item per cell I don’t know if this is possible. What I would like to be able to do is select #Governance_and_Ecosystem and then it shows everything connected to #Governance_and_Ecosystem in each Identifier section along with #Resilience. Also, if I select data in another column that has multiple entries i.e. #Confidentiality #Integrity #Availability it will show everything connected to it.

I don’t know if this makes sense, but I hope so and that some clever person can come up with a solution.

Thanks for your help.

 

[vletm]

Deleted some replies.

 

[Peak Seagull]

Excel file.

 

[vletm]

Yes

 

[Peak Seagull]

Yes

Thank you for your reply but this doesn't solve my problem as it just adds basic slicers to the data. What I would like to do (but I have no idea if it's possible) is to be able to keep each set of attributes attached to each identifier when you choose an individual attribute. So it would need a way that you could just select #Resilience in cell H5 and it would return all of the other Identifiers that contain #Resilience and its associated attributes. I've tried listing them all out separately on individual lines so there is a one-to-one relationship but it get's a bit confusing and doesn't return all of the attributes.

I'm not sure the above makes sense but it is very difficult to describe - probably the only way to do this is by writing some code but I don't really want to go down that route if it can be done in excel.

 

[vletm]

Peak Seagull
H5 has #Governance_and_Ecosystem #Protection#Resilience
How would You select #Resilience in cell H5?
... and ...
What would be Your expected results after that?
... You should show that - - You could add that expected result now eg to Sheet2.

 

[Peak Seagull]

Peak Seagull
H5 has #Governance_and_Ecosystem #Protection#Resilience
How would You select #Resilience in cell H5?
... and ...
What would be Your expected results after that?
... You should show that - - You could add that expected result now eg to Sheet2.

Thanks for getting back to me.

On the attached spreadsheet you will see that I have set out a separate line for each # listed against each Identifier. However, if you select #Governance_and_Ecosystem using the slicer it doesn't show the other #items against the individual Identifier. For instance for Identifier 5.1 I want it to return all of the multiple #items in each column. I've shown on the separate tab what ideally I would like to show for Identifier 5.1 but the same principle would apply to all of the other Identifier's. I don't think this can be done using a basic spreadsheet.

 

[vletm]

Peak Seagull
... from H5 to ...
1) How Excel would know, that now You would like to see 5.1-case?
2) Your other sheet seems to show unique values per column
... some of those could handle with Conditional Formatting
but with some code could do same effect.

 

[Peak Seagull]

Peak Seagull
... from H5 to ...
1) How Excel would know, that now You would like to see 5.1-case?
2) Your other sheet seems to show unique values per column
... some of those could handle with Conditional Formatting
but with some code could do same effect.

Hi, hope you had a nice weekend.

I've attached an updated spreadsheet which shows on the Controls and Attributes (3) tab how the attributes would be set out initially. However, is there a way using conditional formatting (or would it have to be code) that you could reduce the amount of entries in the slicers to just reflect each individual #attribute. For instance by being able to click on #Resilience in the Security domains section I would want it to return everything to the left of it plus show #Governance_and_Ecosystem as the are all attributes of Identifier 5.1.

I have no idea if this makes sense and I think I may have to give up on this. Anyhow, let me know what you think (although I'm sure your fed up with this too!!).

 

[vletm]

Peak Seagull
I waited that You would answer to my question? How...? ... but no?
Without that ... who knows - what do You would like to have?
Now - You seems to do some manually modifications and higher rows ...
... almost same could do as now with the 1st sheet - except there are those duplicates ... which I would take care by code.
But - without answers ... I can only guess.

 

[Peak Seagull]

Peak Seagull
I waited that You would answer to my question? How...? ... but no?
Without that ... who knows - what do You would like to have?
Now - You seems to do some manually modifications and higher rows ...
... almost same could do as now with the 1st sheet - except there are those duplicates ... which I would take care by code.
But - without answers ... I can only guess.

Thanks for trying but I think I will try something else as it's too difficult to explain what I want in writing.

 

[vletm]

Peak Seagull
... hmm?
Do You mean that - You don't know - how to select / control something - which You would like to get?
Could You try to do some samples ..
what to select > what to see?
without any writings ...

 

[Peak Seagull]

If we take Identifier 5.1 for example, if I click on the Security domains slicer labelled #Governance_and_Ecosystem it then only shows row 11 against identifier 1, with only #Confidentiality under the Information security properties heading and #Governance_and_Ecosystem under the Security domains heading. I want it to show all of the other #attributes that apply to that particular identifier. So, it would also show #Integrity and #Availability under the Information security properties heading and also show #Resilience under the Security domains heading.

Controls and Attributes (3) tab shows what I want to see - I don't think this is possible.

 

[vletm]

From this ... You start ... okay?


You wrote that You click #Governance_and_Ecosystem ... okay?

... or did You think about this below?


Normally
... it cannot be middle one ... because there is #Governance_and_Ecosystem
... as well as the lowest one ... because there is #Resilience

Okay, Your output could be possible - but then You should show the clear rules.

 

[Peak Seagull]

 

[vletm]

Peak Seagull
Did You recheck - what did You write about that logic?
This is one sample (eg there are few minor things, which would work other ways).
There are three instructions and one note about those Your texts.
Based Your #15 snapshot:
You seems to want to have one worksheet and none slicers based.
You could do Your selections from Your data.

 

[p45cal]

See if I'm getting warm with the data at cell J1 and the slicer below it.

 

[p45cal]

A couple more offerings:
1. Cell J1 table on Sheet1 and slicer beneath. Works well with identifier slicer filtering for only 1 ID
2. Table and slicer on the Table2 sheet. I think this one gives you what you're asking for.

 

[Peak Seagull]

A couple more offerings:
1. Cell J1 table on Sheet1 and slicer beneath. Works well with identifier slicer filtering for only 1 ID
2. Table and slicer on the Table2 sheet. I think this one gives you what you're asking for.

This looks brilliant.

Some questions:
1. If I have lots of Identifiers (over 90) would I need to put them all into a table first as in Sheet1?
2. What does Table 2 tab do? Is this a Pivot Table created from the table in Sheet1?
3. What does Sheet 2 do and why are some rows hidden?

This is very clever - thanks for your help.

 

[p45cal]

Some questions:

Which offering are you preferring?

 

[Peak Seagull]

Which offering are you preferring?

Hi - I like them both but yes Table2 tab seems best.

 

[p45cal]

Some questions:
1. If I have lots of Identifiers (over 90) would I need to put them all into a table first as in Sheet1?
2. What does Table 2 tab do? Is this a Pivot Table created from the table in Sheet1?
3. What does Sheet 2 do and why are some rows hidden?

1. Add or overwrite the data in Table 1 on sheet Sheet1 ensuring:

• the data in the Identifier column is TEXT (your 5.10 is different from 5.1!)
• in each cell where there are #s, make sure there's a space before a # character in any 2nd or subsequent #word to separate it from the previous #word (a space is used to split the data within a cell)
• remove any hyphens (some are invisible! They're soft hyphens (ascii code 173))
• that the table includes all the new data (drag grab handle in bottom right of table to adjust the size of the table if it's not automatically in the right place

2. It is a pivot table based on a query on the data in sheet Sheet1
3. Sheet2 is the result of a query on the data in Sheet1 but is only used in the solution at cell J1 of Sheet1. Some rows are hidden because of the slicer choices on Sheet1

Attached is a workbook containing only the necessary for Table2 tab.
If you update the data on Sheet1 then be sure to Refresh the pivot table on Table2 tab.
To see what's going on in the background:


The pivot table is based on that data.

 

[Peak Seagull]

This is fantastic I'll have a play with this over the next couple of days. Thank you very much for your help.

 

[Peak Seagull]

1. Add or overwrite the data in Table 1 on sheet Sheet1 ensuring:

• the data in the Identifier column is TEXT (your 5.10 is different from 5.1!)
• in each cell where there are #s, make sure there's a space before a # character in any 2nd or subsequent #word to separate it from the previous #word (a space is used to split the data within a cell)
• remove any hyphens (some are invisible! They're soft hyphens (ascii code 173))
• that the table includes all the new data (drag grab handle in bottom right of table to adjust the size of the table if it's not automatically in the right place

2. It is a pivot table based on a query on the data in sheet Sheet1
3. Sheet2 is the result of a query on the data in Sheet1 but is only used in the solution at cell J1 of Sheet1. Some rows are hidden because of the slicer choices on Sheet1

Attached is a workbook containing only the necessary for Table2 tab.
If you update the data on Sheet1 then be sure to Refresh the pivot table on Table2 tab.
To see what's going on in the background:

View attachment 73882
The pivot table is based on that data.

Hi - Is this done using Power Query? If so will someone be able to use it if they are using Excel 2010 and above? I've never used Power Query so am not familiar with the mechanics of how it works.

 

[p45cal]

Is this done using Power Query?

Yes. AKA Get & Transform data. It's built in now.
See:
Install Power Query Excel 2013 | MyExcelOnline

Install Power Query Excel 2013 | MyExcelOnline

Power Query in Excel can be used to access, clean and transform all that messy data and displays it in a way that Excel can work with. Click here to learn more about Power Query Excel 2013!

www.myexcelonline.com

How To Install Excel 2010 Power Query | MyExcelOnline

How To Install Excel 2010 Power Query | MyExcelOnline

Power Query in Excel can be used to access, clean and transform all that messy data and displays it in a way that Excel can work with.

www.myexcelonline.com