Creating a new system. Excel direction needed.

[Luke1690]

Im currently setting up a new process at work using excel. im after some guidance with what excel method to use.

Currently;
multiple colleagues open (workbook1) and choose from drop down boxes what they need to order and add specific details about their order. (workbook1) is then sent to a another department as an attachment to be ordered. Once the email and order has been sent, the same colleague will then open (workbook 2) a shared document.
and add his order on a new row. (Workbook 2) is used to see how many orders have been placed and at what time.
(different departments also log on to (Workbook 2) to update the status of the order once the item has physically passed through them.

Errors and issues.
Workbook2 has multiple people updating it at the same time and crashes a lot or people are overwriting each others data no matter what share settings i set it at.
Waste time; if you have understood my description you can see that colleagues are having to input the same information twice on two different workbooks.

future
i want Workbook2 to just be a viewing workbook, possibly only edited by a few people with new order status ( the way i want to archive this is when (workbook1) is completed and sent to the first department as an attachment. Workbook 2 automatically inputs the data on a new row and then if another colleague completes workbook 1 with a new order, Workbook 2 adds this to the next row down. ( my question is what excel method/ software should i research/use to help me towards this?)


Thanks in advance.

 

[cmowla]

Hi Luke,

I don't how what the frequency of order placements are, as that is important for deciding which method to choose. If you all use a shared drive that all of you have (and therefore Excel VBA will have) permissions to modify files, that's also very important to know.

Clearly the largest issue is the crashing. I have no doubt you have much more experience than I do regarding Excel's sharing settings, so I will take your word for it!

the way i want to archive this is when (workbook1) is completed and sent to the first department as an attachment. Workbook 2 automatically inputs the data on a new row and then if another colleague completes workbook 1 with a new order, Workbook 2 adds this to the next row down

That's exactly the way I would believe it would be best. If you all use a shared drive, then there would be no need for anyone to send attachments. That is, I (or someone here) can easily put together a VBA script for this.

The way it would work is everyone would be given their own copy of Workbook 1. They never send it to you. When they make edits in it and are ready to send their changes to you, they click on a button of some kind (using a cell as a button . . . it's possible to make double-clicking on a specific cell trigger a program . . . is the most stable form of buttons in Excel) to officially send it. And when they do, the VBA script will create a .txt tile with the edits they made. (I'm assuming that the text they enter . . . not the formatting . . . is what is important.) The text file they (indirectly) create will be sent to a folder on your shared drive from which Workbook 2 will collect and then input the data into Excel. And then when you (or whoever has access to Workbook 2 . . . which can be read-only to most people, as you suggested) click on a button of some kind, it pulls in the data into the workbook "one at a time".

This way, there is no room for conflict. But again, this is dependent on you all using a shared drive. (And this can be made to be done periodically automatically, or it can be (your?) responsibility to pull it in. But fully automating it requires using Windows Task Scheduler. It's a doable process, but you need to have sufficient permissions should that route be most desirable. Edit: You can also use Excel itself to periodically do tasks, but Excel would have to remain opened ALL THE TIME. Windows Task Scheduler can wake up the computer, etc.)

If you all use Microsoft Outlook, then it's possible to make that button (in their Workbook 1) send an email with the .txt file as an attachment to your inbox. Then a VBA script can be written for you to automatically collect the attachments from all such emails (which all can be programmatically tagged in some way . . . maybe all emails have the exact same title that always remains the same) and then pulls in the data from the .txt files that way. (And .txt files are very small in size in comparison to the workbooks that you are probably using, so this shouldn't be a problem for email storage space.)

But from our perspective, it's easiest (and more feasible) if you all have a shared drive, rather than using the email system. (Far fewer steps involved.)