Transferring Data to a form or table

[samwignall]

Purpose of the sheet is a Training Needs analysis, I have over 240 licenses to filter.
In the doc i have reduced down for example purposes.

The training is split into 4 categories (Mandatory, Legal, Future Needs, and If required for specific site)
These categories have timescales applicable to them.

In Tab 1 you will see the layout i have in mind to Filter the position and have it show me all the tickets I need for that role and the number of weeks I have to complete them.

How can i do this?
Please help.

[JeteMc]

This proposal fills row 1 on the Search Form 1 sheet with M1:F4 values which may be hidden by selecting white for the color of the font (as modeled).
The formula that populates C4:C12 is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[samwignall]

Hi Jete,
Is there a way to have the list in Column B (currently manually filled) to be auto populated based on whether or not the training/license is needed for the role selected in the drop down box?

[JeteMc]

One way to do that would be to use the original table in columns B:R as a helper table. (moved down to row 100 in the attached copy of the file).
S100:S108 are then populated using: =SUMPRODUCT(--(C100:R100<>""))
B4:B12 are populated using: =IFERROR(INDEX(B$100:B$108,AGGREGATE(15,6,(ROW(B$100:B$108)-ROW(B$99))/(S$100:S$108>0),ROWS(A$1:A1))),"")
C4:R12 are populated using: =IF($B4="","",INDEX(C$100:C$108,MATCH($B4,$B$100:$B$108,0)))
Let us know if you have any questions.