Hi,
I need your creativity!
Problem:
I work in a support team in a company where we receive approximately 500 orders per day from approximately 200 users in the company. Currently, these 200 users will either call or e-mail to place their orders. We use approximately 2.5 full time people to take and process the orders. Considering the time we spend on calls and e-mails, I think we could save as much time as a full time person if we did away with the bulk of the calls and e-mails and instead automated the collection of data. I want to figure out the best way of automating the data collection.
Tools Available:
Requirements:
A Potential Solution:
This is the best solution I can think of, but I'm not convinced, and would love to know if you have any better ideas:
1. The user opens a password protected Excel workbook placed on a shared drive.
- There is one user workbook for each user
- All users and the support team has access to the shared drive
- Only the individual user and the support team has the password
- I make the workbook as user friendly as possible
2. The user enters his/her order in a table as instructed in that file, and saves.
3. A member of the support team opens a Master Excel workbook and clicks a button which runs a macro which retrieves all data from all 200 user workbooks and compiles them into a single table.
- A macro is necessary because the user workbooks are password protected.
You could potentially build further on this. The support team could enter data in the Master workbook which indicates the status of an order. This information can then be fed back to the user workbooks. But I need to decide on a minimum viable product first, something that's simple enough for me to do in my spare time.
Potential Problems with this Solution
Do you have any suggestions on how I can address these problems? Alternatively, would you suggest some entirely different approach?
I need your creativity!
Problem:
I work in a support team in a company where we receive approximately 500 orders per day from approximately 200 users in the company. Currently, these 200 users will either call or e-mail to place their orders. We use approximately 2.5 full time people to take and process the orders. Considering the time we spend on calls and e-mails, I think we could save as much time as a full time person if we did away with the bulk of the calls and e-mails and instead automated the collection of data. I want to figure out the best way of automating the data collection.
Tools Available:
- Microsoft Excel 2007
- Microsoft Access 2007
- Microsoft Outlook 2007
- Shared drives
Requirements:
- The solution must be quick, easy and intuitive from the users' point of view. Users can use Outlook and have basic skills in Excel. They cannot be expected to use Access.
- Users must be able to submit several orders in one go (which is why Access's built in 'collect by e-mail' function is not sufficient).
- Orders are confidential and it must therefore not be possible for users to read or alter each others' orders.
- The support team (that's me and my colleagues) must be able to access all orders in one unified table in Excel/Access in real time (no more than 10 minutes delay).
- Due to company policy, I will be unable to install any non-approved software.
- Due to company policy, I will be unable to transfer data through a Internet website.
A Potential Solution:
This is the best solution I can think of, but I'm not convinced, and would love to know if you have any better ideas:
1. The user opens a password protected Excel workbook placed on a shared drive.
- There is one user workbook for each user
- All users and the support team has access to the shared drive
- Only the individual user and the support team has the password
- I make the workbook as user friendly as possible
2. The user enters his/her order in a table as instructed in that file, and saves.
3. A member of the support team opens a Master Excel workbook and clicks a button which runs a macro which retrieves all data from all 200 user workbooks and compiles them into a single table.
- A macro is necessary because the user workbooks are password protected.
You could potentially build further on this. The support team could enter data in the Master workbook which indicates the status of an order. This information can then be fed back to the user workbooks. But I need to decide on a minimum viable product first, something that's simple enough for me to do in my spare time.
Potential Problems with this Solution
- I would have to store all 200 users' unencrypted passwords in VBA code, which may not be compliant, even if I generate random passwords for them so that the users don't re-use their passwords.
- Users might enter data in unexpected places and with unexpected formatting. Users might also alter file names and locations of files, or add/remove columns/rows in files etc. The users would be told not to do this, but it's likely that 1 in 200 makes a mistake now and then. This makes the system prone to errors.
- Two people accessing and editing the Master workbook at any one point in time would be problematic. This would not necessarily be a deal breaker at first. During a transition period, we would take orders via calls, e-mails and Excel simultaneously, so it would be sufficient to have one person in the Master file. But once people are used to submitting orders through Excel and realise that these orders are processed faster, we might have so many orders that we need more than one person working in that file at a time.
Do you have any suggestions on how I can address these problems? Alternatively, would you suggest some entirely different approach?
Last edited:
