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), t...
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 ♥
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.
- Nvm, it's not working 😞
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.
- Ohh I think I found my mistake. It's the way I named the sheets !
- 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 !