Excel filter and hide other columns

[BeyK40]

Hi all,

I am curently working on a general table for our company. I've made a table for our partners.

In the table they see some tasks, they all have responsibilities for different tasks, and they recieve points per task. But, they don't need to see the other partners. They now see a complete overview, I want to minimaze that.

So, I want an option to filter partners, and the result has to be that they only see their party and their responsibility. In the filter option, I also want the "All" function, so that they still can see a overall overview ( situation atm).

To show what I want, I've added the table that I use ( but blank), and the result I want.

Who can help me?

Thanks!

Result has to be:

[#2 · Accepted solution]

@BeyK40 

I have attached a small demo workbook. You'll have to modify the code for your situation.

To view the code, right-click the sheet tab and select 'View Code'.

You'll need to allow macros when you open the workbook.

[#3]

Thanks, this was what I needed. But, how can I add this in my project? I've never used it.

[#4]

@BeyK40

You can copy the code from the worksheet module in the demo workbook to the worksheet module in your own workbook.

You'll then have to modify it because your worksheet probably uses different columns for the partners.

You'll have to save it as a macro-enabled workbook (.xlsm).

If you wish, you can attach a copy of your workbook without sensitive data, or make it available through OneDrive, Google Drive, Dropbox or similar.

[#5]

@Hans Vogelaar 

Still doesn't work.. I've copied the VBA and modified it, but it still didn't work.

Below my workbook without data. Ive 5 sheets in one workbook, so I've made a new workbook with only 1 sheet. 

[#6]

@BeyK40

Hopefully @Hans_Vogelaar doesn't mind me jumping in here and you should mark his response as 'Best Answer' as the hard yards are already done....

In your workbook, you have saved the code as modules, whereas a worksheet change routine needs to be in the MS Excel Objects for the specific worksheet. The next thing is that you need a data validation list for the filter items where the code is monitoring for the change. See attached the updated version:

@BeyK40 

[#7]

@Charla74 

Thanks for fixing it!

[#8]

@Hans Vogelaar @Charla74 

Thanks!

One more question.
The cells with te partners refer to another sheet (main sheet). With data valadition there is no problem, still get a list to filter. But the code doesnt work.. Is there a way to fix this?

(the code does work if I just type in the partner, but I it has to refer to the main sheet, because if a partner changes, I wanted it to automatically change in all the sheets.)

[#9]

The 'Worksheet Change' is monitoring for an event to occur specifically in the sheet which is why it works when you update (or change) the value manually....a link to the main sheet does not change, i.e. the formula (=[MainSheet]Sheet1!$A$2, for example) always remains static despite the displayed value reflecting the change made in the main workbook.

[#10]

@BeyK40 

An option would be to use the Worksheet_Calculate event, although the overhead is much larger.

See the attached version.

[#11]

Thanks a lot for the help!
It sadly didn't work to refer te cell to the mainsheet.