Home

To Record Latest attempt....

%3CLINGO-SUB%20id%3D%22lingo-sub-684022%22%20slang%3D%22en-US%22%3ETo%20Record%20Latest%20attempt....%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-684022%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20build%20a%20string%20that%20can%20help%20me%20with%20tracking%20of%20employees%20if%20they%20have%20attended%20last%20course%20or%20not.%20If%20they%20have%20attended%20then%20it%20will%20return%20me%20date%20of%20course%20attended.%20But%20if%20an%20employee%20could%20attend%20the%20course%20then%20I%20get%20the%20same%20reason%20in%20return.%20But%20as%20these%20course%20are%20scheduled%20all%20month%20and%20an%20employee%20will%20be%20attending%20this%20more%20than%20once.%20Then%20I%20have%20a%20problem.%20As%20I%20have%20to%20pre%20schedule%20everyone%20for%20all%20week%2Fmonth%20therefore%20I%20don't%20get%20last%20attendance%20if%20there%20is%20an%20upcoming%20event%20but%20not%20attended%20yet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20example%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-684022%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Estring%20developer%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-684902%22%20slang%3D%22en-US%22%3ERe%3A%20To%20Record%20Latest%20attempt....%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-684902%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F358402%22%20target%3D%22_blank%22%3E%40muzaman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%24E%248%3A%24M%248%2C%0A%20%20%20%20AGGREGATE(14%2C6%2C1%2F(%24E%2410%3A%24M%2410%3D%22PC%22)%2F(%24F%2410%3A%24N%2410%3D%22Yes%22)*%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20(COLUMN(%24E%248%3A%24M%248)-COLUMN(%24D%248))%2C1))%2C%0A%22%22)%3C%2FPRE%3E%0A%3CP%3EI%20only%20unmerged%20cells%20with%20dates%20and%20format%20them%20with%20Center%20Across%20Selection%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-687065%22%20slang%3D%22en-US%22%3ERe%3A%20To%20Record%20Latest%20attempt....%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-687065%22%20slang%3D%22en-US%22%3EThank%20You%20Sergei%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20appreciate%20your%20work.%20I%20can%20get%20similar%20response%20with%20this%20too%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIFERROR(LOOKUP(2%2C1%2F(E3%3AM3%3D%22PC%22)%2F(F3%3AN3%3D%22YES%22)%2C%24E%241%3A%24M%241)%2C%22%22)%3CBR%20%2F%3E%3CBR%20%2F%3Ewhat%20I%20am%20looking%20in%20return%20is%3A%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20attended%20then%20return%20Date%20in%20column%20D%3CBR%20%2F%3EIf%20not%20attended%20then%20NO%2FAA%2FUA%2FExcep(any%20selected%20response)%20in%20column%20D.%3C%2FLINGO-BODY%3E
muzaman
New Contributor

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

2 Replies

@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

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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies