Forum Discussion

muzaman's avatar
muzaman
Copper Contributor
Jun 11, 2019

To Record Latest attempt....

Hi,

 

I am trying to build a string that can help me with tracking of employees if they have attended last course or not. If they have attended then it will return me date of course attended. But if an employee could attend the course then I get the same reason in return. But as these course are scheduled all month and an employee will be attending this more than once. Then I have a problem. As I have to pre schedule everyone for all week/month therefore I don't get last attendance if there is an upcoming event but not attended yet. 

 

Please see attached example file.

 

Thank You

excel
Formulas and Functions
string developer

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 11, 2019

    muzaman 

     

    That could be

    =IFERROR(INDEX($E$8:$M$8,
    AGGREGATE(14,6,1/($E$10:$M$10="PC")/($F$10:$N$10="Yes")*
              (COLUMN($E$8:$M$8)-COLUMN($D$8)),1)),
"")

    I only unmerged cells with dates and format them with Center Across Selection

    • muzaman's avatar
      muzaman
      Copper Contributor
      Jun 12, 2019
      Thank You Sergei,

      I appreciate your work. I can get similar response with this too;

      =IFERROR(LOOKUP(2,1/(E3:M3="PC")/(F3:N3="YES"),$E$1:$M$1),"")

      what I am looking in return is:

      If attended then return Date in column D
      If not attended then NO/AA/UA/Excep(any selected response) in column D.

Resources