Advice on Data Entry

DunnRight

New Member
Joined
Feb 27, 2014
Messages
49
Good day to all. I am looking for some advice on data entry. The company I work for uses Excel 2003 to track purchase orders, repair orders, stock items, and parts being supplied by our customers. We call it our Master Passdown workbook. This book contains tabs for each month. Each month contains hundreds of lines of data for multiple customer projects we are working on. Right now my boss is inputting all information twice. We have to break the Master Passdown into individual workbooks for our customers in order to keep them in the loop about their project. I am trying to come up with a way to avoid the duplicate work. I know no one here likes the idea of a shared workbook and I am open to other suggestions, but the Master needs to be available for update by multiple users at the same time. We have two purchasing agents, a receiving clerk, a shipping clerk, myself and my boss that would all be using the file. I have created an excel file, with the help of several people on this forum, that is very close to what I want. Unfortunately, it won't allow multiple people to use the file at the same time because of a macro that is used on it. The macro is assigned to a button that anyone would use to enter a new line into the sheet. The reason I am doing it this way is because several of the columns have formulas in them and instead of having a bunch of semi-blank rows with the formulas pasted in them waiting on the next entry, I wanted to use a button that would automatically add the next line with the formulas in it. The macro works great. But, as soon as the workbook is shared the macro no longer works. I am able to make the macro turn off sharing add the line and then turn it back on, but that would constantly kick everyone out and cause mass confusion. I am trying to be as descriptive as I can about this, but I am sure if anyone has any ideas there will still be questions. Any ideas on what I can do?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
If you set the data entry sheet up as a table, then the formulas & formatting will automatically extend as new data is entered. That would at least get you past having to use VBA.

As for collaborative editing, take a look at this post from the Excel development team: Updated! Collaborative editing using the Excel Web App - Office Blogs

Regarding keeping monthly data in separate worksheets, I'd avoid that like the plague and put it all in one sheet. That way you can use Pivot Tables to summarize the data quickly and easily. It's nothing to add a Slicer for Customers to prepare customized pt reports that you can send them.

Note that for a true multi-user environment, you'll probably want to consider a database, like Access.
 
Upvote 0
Thank you for the reply. Unfortunately, the table idea doesn't quite work because it is "Lists" in 2003 and you can't share a book that uses them. Access would be great if I knew more about developing what I want to achieve. I started to work on one, but quickly got confused with how to tie things together.
 
Upvote 0
You can set up a table in the Excel Web App.

As for Access, you might want to check out the Template gallery and see if there's something pre-built that might suit your needs with minimal tweaking. There's also an Access forum here, so you can get some good guidance if you decide to go that route.
 
Upvote 0
Did you see if using the FORMS function would make it easier.
 
Upvote 0
I have started to build a sheet that uses the FORMS function. Not complete yet. I want to try and give my boss several options when it comes to this new book. He is very old school and is already very skeptical when I talk to him about any of this. I wish I new more about Access, because that is what everyone is suggesting I go to since it has to be used by multiple people. Right now I have a shared book that works fine, but I am trying to get help with looping through the sheets to pull out unique values to use as reports.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top