Optimizing Recruitment Tracker on Excel
Budget: $10 – $30 USD
Project Description:
We are seeking an experienced Excel professional to review, fix, and enhance a comprehensive recruitment tracker. The file is currently structured to support recruitment metrics tracking but has several functional and formula-related issues that need resolution. Additionally, the tracker must be optimized to integrate with mail merge functionalities for providing weekly status updates and progress tracking to the direct manager and hiring managers.
Scope of Work:
1. Dashboard Fixes:
• Correct the formula for Total Open Vacancies to accurately count open vacancies using =COUNTIF(VacanciesTable[Vacancy Status], "Open").
• Fix the Fill Rate formula to use structured references like =COUNTIF(VacanciesTable[Vacancy Status], "Closed") / COUNTA(VacanciesTable[Vacancy Status]) * 100.
• Ensure Average Time-to-Fill (days) is calculated correctly without duplicate formulas.
• Add slicers or filtering capabilities for interactive data analysis.
2. Vacancies Sheet Fixes:
• Update the Days Open formula to handle open vacancies gracefully (e.g., =IF(F2="", TODAY()-E2, F2-E2)).
• Correct formulas for Total Candidates and Total Hires to ensure they pull accurate data from the Candidates sheet.
• Apply conditional formatting to highlight overdue vacancies (Days Open > 30).
3. Candidates Sheet Fixes:
• Remove unnecessary formulas (e.g., in the Application Date column).
• Replace placeholder formulas in the Dynamic Candidate Search Tool section with functional tools like FILTER or VBA macros.
• Ensure consistent formatting and usability for manual data entry.
4. Form Sheet Automation:
• Add VBA macros to automate data transfer from the Form sheet to the Vacancies and Candidates sheets.
• Create functional buttons for “Add Vacancy” and “Add Candidate” actions.
• Ensure seamless operation and usability of the form.
5. Vacancy Summary Fixes:
• Update the Candidates Applied for Selected Vacancy section to retrieve all matching candidates for a selected vacancy.
• Fix the Candidate Pipeline Distribution section to ensure unique and accurate calculations.
6. Hiring Manager View:
• Dynamically display the number of candidates applied for each vacancy without hardcoded values.
• Ensure data updates automatically when new candidates or vacancies are added.
7. Candidate Matching Sheet:
• Implement a skill-matching algorithm to dynamically identify candidates for specific vacancies using Excel formulas or VBA.
8. Weekly Report Template:
• Replace placeholders with dynamic pivot tables and charts summarizing recruitment activity by department, time-to-hire, etc.
• Ensure the file is ready for mail merge to send weekly updates to managers via Microsoft Word/Outlook.
9. Recruitment Calendar:
• Automate the calendar to pull event dates (e.g., deadlines, interviews) from the Vacancies sheet dynamically.
10. Vacancy Templates:
• Integrate the template sheet with other sheets to dynamically generate pre-filled job templates.
11. General Improvements:
• Add data validation to ensure consistent data entry (e.g., dropdowns for Vacancy Status and Current Stage).
• Use IFERROR or IFNA to handle errors in all formulas.
• Replace hardcoded ranges with structured references or dynamic named ranges.
• Ensure all conditional formatting and metrics work seamlessly.
• Add enhanced visuals, including charts, graphs, and summary sections.
Key Use Case:
This Excel tracker will be integrated with mail merge functionalities to provide weekly status updates to the direct manager and hiring managers. The mail merge will automatically pull data from the tracker, summarizing progress, key metrics, and updates for each vacancy and candidate pipeline.
Deliverables:
• A fully functional and error-free recruitment tracker in Excel.
• VBA macros embedded for automation, including buttons for streamlined use.
• Compatibility with mail merge tools for generating progress reports.
• Documentation on changes made and instructions for future use.
Requirements:
• Expertise in advanced Excel formulas, structured references, and data visualization.
• Proficiency in VBA to automate workflows and add interactive features.
• Experience with integrating Excel for mail merge operations.
We are seeking an experienced Excel professional to review, fix, and enhance a comprehensive recruitment tracker. The file is currently structured to support recruitment metrics tracking but has several functional and formula-related issues that need resolution. Additionally, the tracker must be optimized to integrate with mail merge functionalities for providing weekly status updates and progress tracking to the direct manager and hiring managers.
Scope of Work:
1. Dashboard Fixes:
• Correct the formula for Total Open Vacancies to accurately count open vacancies using =COUNTIF(VacanciesTable[Vacancy Status], "Open").
• Fix the Fill Rate formula to use structured references like =COUNTIF(VacanciesTable[Vacancy Status], "Closed") / COUNTA(VacanciesTable[Vacancy Status]) * 100.
• Ensure Average Time-to-Fill (days) is calculated correctly without duplicate formulas.
• Add slicers or filtering capabilities for interactive data analysis.
2. Vacancies Sheet Fixes:
• Update the Days Open formula to handle open vacancies gracefully (e.g., =IF(F2="", TODAY()-E2, F2-E2)).
• Correct formulas for Total Candidates and Total Hires to ensure they pull accurate data from the Candidates sheet.
• Apply conditional formatting to highlight overdue vacancies (Days Open > 30).
3. Candidates Sheet Fixes:
• Remove unnecessary formulas (e.g., in the Application Date column).
• Replace placeholder formulas in the Dynamic Candidate Search Tool section with functional tools like FILTER or VBA macros.
• Ensure consistent formatting and usability for manual data entry.
4. Form Sheet Automation:
• Add VBA macros to automate data transfer from the Form sheet to the Vacancies and Candidates sheets.
• Create functional buttons for “Add Vacancy” and “Add Candidate” actions.
• Ensure seamless operation and usability of the form.
5. Vacancy Summary Fixes:
• Update the Candidates Applied for Selected Vacancy section to retrieve all matching candidates for a selected vacancy.
• Fix the Candidate Pipeline Distribution section to ensure unique and accurate calculations.
6. Hiring Manager View:
• Dynamically display the number of candidates applied for each vacancy without hardcoded values.
• Ensure data updates automatically when new candidates or vacancies are added.
7. Candidate Matching Sheet:
• Implement a skill-matching algorithm to dynamically identify candidates for specific vacancies using Excel formulas or VBA.
8. Weekly Report Template:
• Replace placeholders with dynamic pivot tables and charts summarizing recruitment activity by department, time-to-hire, etc.
• Ensure the file is ready for mail merge to send weekly updates to managers via Microsoft Word/Outlook.
9. Recruitment Calendar:
• Automate the calendar to pull event dates (e.g., deadlines, interviews) from the Vacancies sheet dynamically.
10. Vacancy Templates:
• Integrate the template sheet with other sheets to dynamically generate pre-filled job templates.
11. General Improvements:
• Add data validation to ensure consistent data entry (e.g., dropdowns for Vacancy Status and Current Stage).
• Use IFERROR or IFNA to handle errors in all formulas.
• Replace hardcoded ranges with structured references or dynamic named ranges.
• Ensure all conditional formatting and metrics work seamlessly.
• Add enhanced visuals, including charts, graphs, and summary sections.
Key Use Case:
This Excel tracker will be integrated with mail merge functionalities to provide weekly status updates to the direct manager and hiring managers. The mail merge will automatically pull data from the tracker, summarizing progress, key metrics, and updates for each vacancy and candidate pipeline.
Deliverables:
• A fully functional and error-free recruitment tracker in Excel.
• VBA macros embedded for automation, including buttons for streamlined use.
• Compatibility with mail merge tools for generating progress reports.
• Documentation on changes made and instructions for future use.
Requirements:
• Expertise in advanced Excel formulas, structured references, and data visualization.
• Proficiency in VBA to automate workflows and add interactive features.
• Experience with integrating Excel for mail merge operations.