Self-check of employee folders and letter requesting missing documents
HeleneThouny
-
helenethouny -
helenethouny -
Hello, Hello,
I want to create an Excel workbook containing 3 sheets:
1st sheet: a table with formatting, in the first row the employees' names and in the first column the names of the documents that make up the paper file. In the cells next to each document title, I record either NC for non-compliant, or OK for compliant, or NA for not applicable.
2nd sheet: my lists for my databases
3rd sheet: my reminder letter for missing documents. I need that the letter auto-updates when I change the employee's name and that the missing documents for their file appear as a list.
I managed to create for the first employee with an IF formula a first letter that lists the missing documents. I struggled but it correctly includes the document name when NC is noted in the corresponding cell.
BUT, I don’t know how to make the reference column switch to the selected employee when I change employees. I’m not sure either whether the employee’s name should be entered by a formula like = or if it can come from a database...
In any case, thanks to anyone who can help
Have a good day!
Configuration: Windows / Chrome 80.0.3987.122
I want to create an Excel workbook containing 3 sheets:
1st sheet: a table with formatting, in the first row the employees' names and in the first column the names of the documents that make up the paper file. In the cells next to each document title, I record either NC for non-compliant, or OK for compliant, or NA for not applicable.
2nd sheet: my lists for my databases
3rd sheet: my reminder letter for missing documents. I need that the letter auto-updates when I change the employee's name and that the missing documents for their file appear as a list.
I managed to create for the first employee with an IF formula a first letter that lists the missing documents. I struggled but it correctly includes the document name when NC is noted in the corresponding cell.
BUT, I don’t know how to make the reference column switch to the selected employee when I change employees. I’m not sure either whether the employee’s name should be entered by a formula like = or if it can come from a database...
In any case, thanks to anyone who can help
Have a good day!
Configuration: Windows / Chrome 80.0.3987.122
4 answers
-
Hello Hélène
Help us help you; providing a file would be a good start.
Go to the CJOINT.COM site
select your file (it must not contain personal data)
select a duration
create a link
Copy this link
Then paste it here on CCM in your reply -
Hello,
Thank you for the advice, here is the link:
https://www.cjoint.com/data/JCfseQ0VNz1_test-auto-contr%C3%B4le-dossiers-salari%C3%A9s.xlsx -
Hello Hélène
The link doesn’t work
Change the file name; do not put accents on vowels
--
C’est en forgeant que l’on devient forgeron. - C’est au pied du mur que l’on voit le maçon - on apprend toujours de ses erreurs -
Thank you for your help, my problem has been solved on another forum where I managed to get my file on the first try ^^.
Now I need to know how to close the post on this page? Can you help me again?
(if you’re interested, here is the proposed solution that was given:
=IFERROR(INDEX('AUTO CONTROLE'!B:B;SMALL(SMALL(IF(INDEX(Tableau5[[M. X]:[Colonne17]];;MATCH($E$8;Tableau5[#Headers;[M. X]:[Colonne17]];0))="NC";ROW(Tableau5[Nom Prénom]);"" );ROW(OFFSET($A$1;;;COUNTIF(INDEX(Tableau5[[M. X]:[Colonne17]];;MATCH($E$8;Tableau5[#Headers;[M. X]:[Colonne17]];0));"NC");)));ROW(A1));1);"")
I think that gives you an idea of the logic for an expert without having the file, thank you anyway for your help! )
Best regards
