Excel : IF/THEN on multiple column and ANY sheets

Occasional Contributor

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 : NameB : Details C : DetailsD : Details E : DetailsF : Training ?
Test............Yes / No

 

Sheet #2 (3,4,5 and so on) : Training MM-YY

 

A : NameB : Details C : DetailsD : Details E : DetailsF : Training ?
Test............Yes / Absent / To do again
10 Replies

@AudreySC 

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 ♥

@AudreySC 

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.

Hi Hans,

Thanks again for your quick response ! I've been trying since the past 2 hours to adapt the formula to my excel file, and yet, it's not working But in your Excel it's ok ! I have #REF and #VALEUR mistakes in 90% of my column F. And on the other 10%, it says NO, even if the name of the participant is written in a training sheet.
I used the exact same column as you, to be certain that there would not be any mistakes, and yet !
Ohh I think I found my mistake. It's the way I named the sheets !
Nvm, it's not working

@AudreySC 

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.

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 !

@AudreySC 

I have replied to your private message.

Thank you !!! It worked