calculated column help - identifying current employees based on if one of their personnel IDs is active

[Stacey McWaters]

Hi all. I have attached some sample data to help explain what i am after.

Column B are the approvers of the forms in Column A. Column C has their personnel ID at the time of approving the form, some of the peoples IDs have changed over time. Column D states if the personnel ID in Column C is the current one for that person. I want to calculate a column to dertmine if the person is an active employee or not. So basically if there is a 'Y' against any name i want a 'Y' against all of that persons rows.

e.g Mark Bradley has 3 different IDs, only one is his current ID- i want the calculated column to put a Y against all his rows. similarly John Yates doesnt have a current ID so he should have 'N' against all his rows.

Thanks in advance :)

[Gaia Paolini]

You should be able to do it with this calculated column:

Max([Current Role]) over ([Approver])

which uses the fact that N is alphabetically before Y, so if there is a Y it picks it up as Max(..)

[Stacey McWaters]

That has worked like a charm, thanks Gaia! I havent used the OVER function before so I just wasnt familiar with how to write it.. It seems so simple when you say it like that!