Forum Discussion
Excel : IF/THEN on multiple column and ANY sheets
Hi,
I would like to create an Excel file that will allow me to see if a participant has done a certain training or not. I have a sheet in Excel with the name of the participants (in column A), the details of this participant on the columns B to E and in column F, it is written if YES or NO the participant completed the training.
In the same Excel file, but on other sheets (one sheet per date of training), I have the name of the participant in column A and I asked the trainer to indicate in column F if the participant did the training ("YES"), or if the participant was absent or must do the training again. So for this column F, there is a list with 3 options : YES, ABSENT and TO DO AGAIN.
My question is : in the first sheet (so the one with the name and details of the participants) is it possible to do a formula in column F that will indicate YES if the name of the participant AND the text "YES" appears in any other sheet. And "NO" if the name of the participant does not apprear in any other sheet OR if the name of the participant does appear, but the mention YES in column F (in the sheet of the training date) is NOT there ?
I've been looking for the past few hours on your site and on internet in general.... I think it has to do with the formula LOOKOUT ? But honestly... that is wayyyy over what I'm capable of in Excel hahaha
Kindly,
Audrey
EXAMPLE :
Sheet #1 : Details of participants
| A : Name | B : Details | C : Details | D : Details | E : Details | F : Training ? |
| Test | ... | ... | ... | ... | Yes / No |
Sheet #2 (3,4,5 and so on) : Training MM-YY
| A : Name | B : Details | C : Details | D : Details | E : Details | F : Training ? |
| Test | ... | ... | ... | ... | Yes / Absent / To do again |
See the attached sample workbook. I didn't bother about formatting.
On the first sheet, I used a list of the other sheets to enable counting across them.
The formulas in column F use INDIRECT in combination with this list.
- Hi Hans !
Thank you so much for your quick response 🙂
Could you explain me the purpose of the column K in your formula ?
Does it mean that I have to create an other column in my first sheet (participant details) with the list of all the other sheets (training dates) ?
Just to make sure I understand properly 🙂
Kindly,
Audrey
PS : You are amazing !! Thank you so much ♥Yes, you have to create a range of cells on your first sheet with the names of your training data sheets.I used column K, but it can be anywhere you want. Adjust the formulas in column F accordingly.
