A few button clicks
Update POs and work orders without manual lookups.
Client project · EXCEL VBA AUTOMATION
Automating shortage tracking across BOMs, purchase orders and work orders
A few button clicks replace manual lookups across BOMs, purchase orders and work orders, with shortage and past-due flags built into the workbook.
Client: Aisha
Abhishek
Abhishek Singh
Akhilesh
Prahalad
Shakti
Meet the specialists behind your project.
A remote team of Excel and spreadsheet specialists. You work directly with the person building your file, from the first question to the final handover. More about us.
- FreeScoping & estimate
- 24hTypical reply
- 100%Files & IP yours
The challenge
What needed to change.
Aisha tracks material shortages for assemblies using a bill of materials (BOM) in Excel. Checking each item against open purchase orders and work orders meant manually matching data from several reports. It was slow, and it was easy to miss what was short, what needed a PO, and what was past due.
What we did
The work inside the file.
- 01
Built Excel macros that pull purchase order data, including supplier, quantity due and promise date, and work order data, including status, assembly and shortages, into the BOM sheet.
- 02
Wrote every step to find columns by header name instead of position, so columns can be added or reordered without breaking the workflow.
- 03
Added flexible naming so the workbook accepts alternate headers such as “Shortages” or “Work Order Remarks”, and finds the work order sheet even if its name is slightly different.
- 04
Automated the flagging logic, marking items as short or clear and adding “PO required” and “Past Due” remarks.
- 05
Put the main steps on one-click buttons and added a reset option for testing and re-runs.
- 06
Debugged issues with Aisha along the way, including date display and button assignments pointing to older macro versions.
The results
What changed for the client.
Approximate time savings relate to this project and its workflow.
Automatic flags
Shortages, required POs and past-due items are marked automatically.
Existing layout retained
The workbook adapts to Aisha’s sheets without rebuilding how she works.
~25 hours per week
Approximate time saved on this client’s shortage-tracking process.
Help with a similar project.
Start with the problem
Want to improve your own workbook?
Describe the task and attach a sample file. Choose a quote by email or a free call.
Post your project