Check given set of conditions and return lowest date in other table

[jhonneyboy]

I'm struggling alot with comparing data and show a date. I'm just not able to get it working..
I've added a example workbook to show how the data looks like.

What I am trying to do is, compare the conditions in sheet (competence per station) and compare them with the dates a person/number got the competence. (Date Competence per person).

So the conditions for station 10, are to have the competence A to I. These conditions will be remembered, this means the range A to I will be compared with the person's competences and give the lowest value (oldest date).

It doesn't always be the same range, it could differ if you look at the conditions sheet. Example, station 25 has A to I and K, N & O. For this station it needs to compare these competences/cells with the other table containing the dates of a person and then show the oldest date.

It would be great if it is possible to show it in the 'permission' sheet instead of yes/no.

Is it even possible what I want in VBA?

[Zodeeak]

Have you tried using the SMALL function in your formulae?

[jhonneyboy]

I just need to have a piece of code that remembers the columns in a range with cells containing "x". Then it finds the smallest number in those columns, but on a different sheet. I need this like in a function, so a formula. I just have no clue where to start. Small is probly the way to go, but how do I let the macro remember the columns with x's?

[jhonneyboy]

Ok, i managed to get the ranges myself

Please Login or Register  to view this content.

This shows the ranges that I need to have in a formula in the cells with the formula.

Now I only need to have a formula that calculates the minimumof the range, RangeX. I keep getting #VALUE! when I add:

Please Login or Register  to view this content.

Can't seem to get that to work, any help?
How do I get the indirect function into this code?

[Zodeeak]

The way I worked it out, if I'm understanding your needs, correctly, is, in the cells on the Permission tab, I changed the formulae to the following:

=IF(SUMPRODUCT(('Competence per person'!$B2:$AA2="x")*('Condition per station'!$B$2:$AA$2="x"))>=COUNTIF('Condition per station'!$B$2:$AA$2,"x"),SMALL('Date Competence per person'!B2:AA2,1),"no")

Is that what you're looking for, or am I not understanding your needs, correctly?

-Z