Filter only the columns based on another filter?

[terry3218]

hi all, i would really appreciate if someone can help me out with this. So i have 2 sheets, 1 sheet would have list of training needed for each profession and other sheet will have the names of all the people along with all the trainings. Now, in the 2nd sheet when I filter a certain profession, i want all the trainings that are not required for that profession to be hidden? example attached. If i select accountant in the profession in the 2nd sheet, i only want to see outlook and excel.

thanks

this question is cross-posted on another forum

https://www.mrexcel.com/board/thread...ilter.1138357/

[terry3218]

here is an example attached

[terry3218]

Anyone can please assist?

[miahallen]

I don't understand your expected result, so I'm not sure how to answer your question.

[alansidman]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

[terry3218]

thanks, i have updated my original post

[terry3218]

I don't understand your expected result, so I'm not sure how to answer your question.

i have attached a sample workbook with expected result

[JeteMc]

Somewhat of a roundabout way that assumes only one profession to be selected in the filter.
Populate column C using: =SUBTOTAL(103,B2)
Populate D1:G1 using: =IF(COUNTIFS(INDEX(Training!$A2:$C4,,MATCH($J1,Training!$A1:$C1,0)),L1),L1,"")
Populate J1 using: =INDEX(B2:B3,MATCH(1,C2:C3,0))
Note that column C as well as cell J1 and L1:O1 may be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.

[terry3218]

Somewhat of a roundabout way that assumes only one profession to be selected in the filter.
Populate column C using: =SUBTOTAL(103,B2)
Populate D1:G1 using: =IF(COUNTIFS(INDEX(Training!$A2:$C4,,MATCH($J1,Training!$A1:$C1,0)),L1),L1,"")
Populate J1 using: =INDEX(B2:B3,MATCH(1,C2:C3,0))
Note that column C as well as cell J1 and L1:O1 may be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.

thanks. this works. However, I can see lot of blank columns if that training is not applicable to that profession. Is there a way to not have those blank column display the name of the previous or next visible column without duplicating?

[JeteMc]

The formula in cells D1:G1 has been changed to read:

Please Login or Register  to view this content.

The zero values that this formula produces in some cells is hidden using conditional formatting by setting the font of cells with a value of zero to white.
(Please do not quote whole posts - use the Quick Reply window instead)
Let us know if you have any questions.

[terry3218]

that's very helpful. thanks.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.