Macros: how to make a chart over how many..

Hi Sanex,

Can you post the sample file so that we can help you better.

Is it necessary to use a macro? My first thought is to use a simple autofilter: https://www.wikihow.com/Use-AutoFilter-in-MS-Excel Once you have set up the autofilter, then it should be as simple as clicking on the dropdown arrow on the desired column, select the desired filter options, and Excel will display only the rows that meet the filter criteria that you specify. You can even filter on multiple columns. For example, you could specify to filter on the Output column value UV 339 and Status column value 2 to show only those who are in training for the UV 339 analysis.

If that will work, that seems easier than writing your own "filter" macro/code/procedure to accomplish this task.

I would recommend a pivot table with slicers (since you have 365) - there are two issues.
1. The file is in XLS format. It would have to be updated to 2010 or better.
2. There are embedded blank rows in the data. This makes auto-detection of the range to convert to a pivot table impossible, so you will have to select the range manually.

In the attached, I converted the spreadsheet to a current version. I converted the data range into a Table (so when you add data, the pivot table will pick it up automatically. I made a pivot table of the data and added a slicer.

like when i hit the search button, i get up a new window (userform) that tells me which people can do the analysis i searched for..

I don't know if you are familiar with autofilter, but this sounds exactly like what you get from the Text Filter-> menu options in the autofilter dropdown menu (this is how I almost always use autofilter). A user selects the autofilter dropdown, clicks on text filter, selects the desired option (or just click on custom), then enters the name (or number or other text) in the dialog fields to search for.

Is that more complex than your colleagues can handle with a little bit of training?

If I decided that I had to simplify even further, I would probably start by using the macro recorder to record me filtering on a criteria, then decide how to proceed from there. Here's what I got from the macro recorder for a simple filter operation:

Please Login or Register  to view this content.

From there, I would need to decide how to easily get value for the "Field" and "Criteria1" arguments and put them into this statement.

And i really need it to be view very simply, so its easy to see.. not to scroll through a list of 40 names..
and also there are about 60 analysis that are gonna go into this sheet, and 40 people (and counting), so i kinda need it to be simple and quick to understand and to draw information out of it..

1.) 60 analyses/60 buttons. Would a drop down also be acceptable?

2.) 40 people and counting ...

Analysis: UV A
Technical Personnel: Vic, Chen
Personnel in training: Ali

Piggybacking on dflak's Table idea I have a formula/UDF solution in mind for your proposed layout.
But how well would that work if there were 20 or more qualifying Technical Personnel?
That's a long concatenated list which I am guessing wouldn't be practical to read.

Having said all that the attached uses dflak's table, a UDF by tigeravatar, a formula that returns a list of unique Output, a dynamic named range and a drop down.

The UDF is 'Concatall'. It does what it says.

In L2 is an array formula filled down to L105. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

The dynamic named formula defines the data validation list. It is in Name Manager as UV_IR

Formula:

Please Login or Register  to view this content.

Then in G2 is a drop down. The Source:

Formula:

Please Login or Register  to view this content.

Then the formula in G3 and filled down to G4 is array entered.

Formula:

Please Login or Register  to view this content.