May 16 2022 09:16 AM
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 |
May 16 2022 10:13 AM
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.
May 16 2022 11:01 AM
May 16 2022 11:05 AM
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.
May 16 2022 11:24 AM
May 16 2022 11:28 AM
May 16 2022 11:33 AM
May 16 2022 11:44 AM
If you don't have Microsoft 365 or Office 2021, you may have to confirm the formula with Ctrl+Shift+Enter each time you edit it.
And keep in mind that I created only 4 extra sheets, so I used $K$2:$K$5. If you have 25 training date sheets, for example, use $K$2:$K$26.
May 16 2022 01:30 PM - edited May 16 2022 01:32 PM
Hi @Hans Vogelaar :)
Thanks again for your quick response !
Yes I have actually eleven sheets, so I changed K5 for K13.
Weirdly, the first 10 participants are working. But when I get to the participant at F14, I have the problem #VALEUR.
As well, it seems to work for the line HARRY POTTER, but not the line SHER ?
Wath am I doing wrong ?
Edit : sorry.... can't seem to be able to add my .xlsx file. It says that it is not supported.
Thank you so much for your help !
May 16 2022 01:59 PM
I have replied to your private message.
May 25 2022 07:15 AM