CODEWITHSUNDEEP
excelexcel-formulavba
Chris Vermeijlen
Chris Vermeijlen

Reputation: 79

get certain years out of other sheets

At work we have a worksheet with serveral (40-50) worksheets. Each worksheets represent a certain study. Several people are on each sheet with a name, surname, start of study (date), birthday, etc. And we have one worksheet with an overview. (worksheet study 1, worksheet study 2, worksheet study 3).

Is there any way, that I can place a column on overview, and filter on the people that have joined a study. (so all people that joined a study in year 2016, but not those that did not.) I have a column with the start date of a study, but people can join a study after a study has started.

I've already tried to use a filter, and tried some stuff with data validation, but Im nowhere close to a a solution. Is there anyone who can put me on the right track?

Upvotes: 0

Views: 37

Answers (1)

SlashnBurn92
SlashnBurn92

Reputation: 78

I'm making some assumptions here:

  • I'm assuming that each worksheet for a study is in the same format
  • The people in a study (the data you want) is in a standardised location on each sheet

One solution would be to do some code in VBA. You could iterate through each sheet (that is not called 'Overview', obviously) and assign to a variable the range where the details you want to check are stored, then iterate through each cell in that range, and record in an array the names of anyone who matches the criteria.

For example, if each of your Study sheets has a list of participants like this: Participant List

You would define the range variable as [Worksheet].Range("A5:A12"), and say:

For Each <cell> in <range>.Cells
If Year(<Worksheet>.Cells(<cell>.Row, <cell>.column + 1).Value) = 2017 Then
<assign the contents of <cell> to an Array>
Next <cell>

Once you'd gone through each worksheet, the contents of the array could be output to any desired location, either on the Overview sheet or elsewhere. This would be a basic idea of one way to do it. I would stress though that your data sounds like exactly the kind of thing that an MS Access Database would be ideal for (I know from personal experience however that some companies/departments are afraid of anything that isn't Excel, so I feel your pain!)

Upvotes: 1

Related Questions