Excel File Tracker Fix
Budget: $10 – $30 USD
I need an Excel and automation specialist to edit and fix an Automated OneDrive File Tracking System in excel that uses macros
This sheet Scans a defined folder for subfolders (each representing a customer).
Lists customer names in column B
Checks for the presence of specific files within each customer's folder.
Applies color-coded visual indicators based on whether the files exist:
Green = found
Red = not found (for critical files)
Grey = optional/missing but not critical (like .2do or Credit Application)
Adds a calculated progress percentage in Column J using a formula
Key Requirements
1. I need to edit the sheet to remove a column from the table that references a file no longer needed. Specifically the Final validation column is no longer needed.
2. I need the sheet to reference the subfolder name and populate the data into 2 different columns. That is to say the subfolders are named "[rep] - [company name]" and right now the spreadsheet lists the whole subfolder name under company name in column B and leaves the rep column under A blank.
3. I need the way the 1 column is being handled to change. Right now Under the "Notes" column the macro looks for the presence of a .2do file and colors the corresponding cell in the table green if its there and grey if it's not. I don't want this cell of the table colored at all I want the file name of the .2do file listed if present and left blank if not
4. I think the macro is currently case and word sensitive. I want it to scan the whole subfolder and turn the cell green if any files in the subfolder contain the words in the column header. For example if the column is titled "New Customer" I want the cell for that column to turn green if it detects a file in that companies subfolder with the title "New Customer", "New-Customer", or "vendor new customer"
5. Highlight rows where all expected files are found (100% progress)
6. (optional for $10 bonus/tip) Use Constants for Columns and Header Row
Then use HEADER_ROW and START_ROW instead of hardcoding 1 and 3.
Ideal Skills:
- Proficiency in Excel and OneDrive
- Experience with automation scripts
This sheet Scans a defined folder for subfolders (each representing a customer).
Lists customer names in column B
Checks for the presence of specific files within each customer's folder.
Applies color-coded visual indicators based on whether the files exist:
Green = found
Red = not found (for critical files)
Grey = optional/missing but not critical (like .2do or Credit Application)
Adds a calculated progress percentage in Column J using a formula
Key Requirements
1. I need to edit the sheet to remove a column from the table that references a file no longer needed. Specifically the Final validation column is no longer needed.
2. I need the sheet to reference the subfolder name and populate the data into 2 different columns. That is to say the subfolders are named "[rep] - [company name]" and right now the spreadsheet lists the whole subfolder name under company name in column B and leaves the rep column under A blank.
3. I need the way the 1 column is being handled to change. Right now Under the "Notes" column the macro looks for the presence of a .2do file and colors the corresponding cell in the table green if its there and grey if it's not. I don't want this cell of the table colored at all I want the file name of the .2do file listed if present and left blank if not
4. I think the macro is currently case and word sensitive. I want it to scan the whole subfolder and turn the cell green if any files in the subfolder contain the words in the column header. For example if the column is titled "New Customer" I want the cell for that column to turn green if it detects a file in that companies subfolder with the title "New Customer", "New-Customer", or "vendor new customer"
5. Highlight rows where all expected files are found (100% progress)
6. (optional for $10 bonus/tip) Use Constants for Columns and Header Row
Then use HEADER_ROW and START_ROW instead of hardcoding 1 and 3.
Ideal Skills:
- Proficiency in Excel and OneDrive
- Experience with automation scripts