Company training file - select target group from list then select employees that attend

Hi all

It's been a while since I've had to put together a solid excel database and my skills are really rusty! I'll explain what I'm aiming to do because there's a fair chance that you know a better way to solve this than my formula.

I have to design a company training database, which shows which employee(s) have to attend which training based on a defined target group list in the database sheet, which lists all the names of that target group in separate columns/named ranges. There are around 10 target groups which overlap, i.e. an employee may be in about 3-4 target groups.

So on the main sheet you enter the name, date of a new training module and which target group it is for. Or you add a new name in a target group ("new employees") and want to see which training modules he or she has to attend when.

The goal is to select the target group of a new training module from a defined list (linked to database sheet) and then have an 'x' appear under each employee name included in that target group.

My best guess is:

=IF(target group selection = target group name, COUNTIF(target group range,name above column))

This works just fine for one instance only. But since I have to check about 10 target groups or more the results turn up as FALSE when several COUNTIFs have the name in it.

=IF(target group selection = target group A, COUNTIF(target group range A,name above),IF(target group selection = target group B, COUNTIF((target group range B,name above)

Would appreciate any idea how to make this work, thanks!

Try

H5

Formula:

Please Login or Register  to view this content.

Copy across

There are no E-mail addresses listed in your workbook - where would Excel find them?

We will leave it this time, but for future reference, this is a different query and really should have been raised in a new thread.

Thanks, the email addresses could be added next to the employees' names in the database worksheet. I'll upload it for reference if necessary

Yes, that would be helpful. Always try to provide a sample file.

I tried to transpose all values in the database in order to have easier formulas (on lines instead of columns) and avoid mistakes.
I redefined the ranges and corrected the above formula as I understood it, unfortunately it doesn't seem to work that way.

Could you please correct it for me and explain where I went wrong so that I could learn? Thanks a lot again!

Additionally if you know of a better way to copy all attendants' emails into 1 cell to copy that would be great

Will you please clear the thing what you want? Explain the problem and how you wish to solve the pattern?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

ok try

Formula:

Please Login or Register  to view this content.