SOLVED

Formula to create a dynamic checklist

%3CLINGO-SUB%20id%3D%22lingo-sub-2487795%22%20slang%3D%22en-US%22%3EFormula%20to%20create%20a%20dynamic%20checklist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2487795%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20trying%20to%20generate%20a%20dynamic%20checklist%20based%20on%20the%20maintenance%20schedule%20as%20shown%20in%20the%20attached%20excel%20file%20%22Maintenance%20Dynamic%20checklist%22.%20%26nbsp%3BThe%20'X'%20indicates%20the%20tasks%20required%20under%20the%20specific%20scheduled%20maintenance.%20%26nbsp%3B%20%26nbsp%3BThe%20second%20table%20in%20the%20spreadsheet%20shows%20the%20outcome%20I%20would%20like%20to%20achieve%3A%20%26nbsp%3BIt%20should%20retrieve%20the%20Task%20ID%20and%20the%20Task%20Description%20based%20on%20droplist%20in%20N1.%20%26nbsp%3BAll%20blanks%20row%20to%20be%20excluded.%20%26nbsp%3B%20%26nbsp%3BI%20hope%20the%20community%20could%20help%20me%20in%20this%20solution.%20%26nbsp%3BThanks.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2487795%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2487847%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20create%20a%20dynamic%20checklist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2487847%22%20slang%3D%22en-US%22%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1088661%22%20target%3D%22_blank%22%3E%40choongko%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3ETbh%2C%20I%20have%20problems%20on%20understand%20detailed%20what%20you%20want%20%3A'D%3CBR%20%2F%3ECan%20you%20try%20to%20be%20more%20precise%20with%20maybe%20an%20example.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3ESchnittlauch%20%5E%5E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2487867%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20create%20a%20dynamic%20checklist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2487867%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1088661%22%20target%3D%22_blank%22%3E%40choongko%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20attached%20a%20solution%20that%20will%20work%20in%20all%20versions%20of%20Excel.%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Excel%20in%20Microsoft%20365%2C%20you%20can%20use%20a%20much%20simpler%20formula%20with%20the%20new%20FILTER%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2487871%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20create%20a%20dynamic%20checklist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2487871%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWow%2C%20thanks%20for%20solution.%20%26nbsp%3BIt%20looks%20complicated.%20%26nbsp%3BMy%20workplace%20is%20using%20the%20older%20version%20of%20excel.%20%26nbsp%3BHowever%2C%20I%20have%20subscribed%20to%20365.%20%26nbsp%3BFor%20learning%20purpose%2C%20could%20you%20share%20the%20simpler%20solutions%20for%20365%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi, 

I have been trying to generate a dynamic checklist based on the maintenance schedule as shown in the attached excel file "Maintenance Dynamic checklist".  The 'X' indicates the tasks required under the specific scheduled maintenance.    The second table in the spreadsheet shows the outcome I would like to achieve:  It should retrieve the Task ID and the Task Description based on droplist in N1.  All blanks row to be excluded.    I hope the community could help me in this solution.  Thanks. 

 

 

 

5 Replies
Hi @choongko

Tbh, I have problems on understand detailed what you want :'D
Can you try to be more precise with maybe an example.

Schnittlauch ^^
best response confirmed by choongko (Contributor)
Solution

@choongko 

I have attached a solution that will work in all versions of Excel.

If you have Excel in Microsoft 365, you can use a much simpler formula with the new FILTER function.

@Hans Vogelaar 

 

Wow, thanks for solution.  It looks complicated.  My workplace is using the older version of excel.  However, I have subscribed to 365.  For learning purpose, could you share the simpler solutions for 365? 

@choongko 

Here is the version using the FILTER function.

Oh my goodness... It's so much simpler. Thanks a lot for your help. Really appreciate it.