Excel 2016 - Do I need VBA to accomplish my task?

[anneparker629]

I am unfamiliar with VBA, but the more I research what I need to accomplish, the more it seems that it might be my best bet.

I am creating a workbook for a small business that has been paper-and-pencil for decades and none of the users are comfortable or familiar with Excel, tablets, smartphones, etc. I have set up a spreadsheet containing all of the data that they need to be able to reference more readily (hence in a spreadsheet as opposed to digging through paper files) and have hired a student to continue manually-inputting the handwritten data retroactively.

The second part of this project is where I have the question. I created a second worksheet in the same workbook that contains a fillable-form-version of the paperwork they have so far been completing by hand. The idea is that each of these users will take a tablet out into "the field" each day and record the data in the fillable form as opposed to on a clipboard, and - ideally - all they would need to do is hit save at the end of each day...and I would like for their data to auto-fill into the first worksheet (creating a new row of data for each form "saved").

I know how to link values from one cell to another (cross-sheet as well as cross-workbook)...but how do I create it so that each of the approx 9 different tablets can sync to the same Master worksheet without trying to write to the same row and/or alter the Master (besides adding their new data and that's it). This Master worksheet is stored in a shared work folder that ANYONE who has access to their wifi could conceivably access and have full admin rights. I imagine I will need to set admin privileges and have each user have Read-Only rights to the Master. Then will I, as the admin, need to manually take each of their new documents and add them to the Master?

Basically, I'd like to set the form/workbooks up to merge correctly and to minimize the "technical" steps that each user would be required to make.

I'm sorry this is so wordy, I imagine it's a fairly simple task for most of you but I'm driving myself nuts just going in circles with my research and what I currently know how to do in Excel.

[This is an example with realistic-looking data of what Spreadsheet 1 looks like - this is what the Master looks like][This is an example of Spreadsheet 2 - the fillable form that each user will complete and save throughout the day and whose data will need to autofill to the Master once they are back in the office]

Any suggestions, direction, or even an "Uh DUH" explanation would be VERY MUCH APPRECIATED!

 

[FDibbins]

To use a form to capture/fill in data will require VBA (which I am weak at)

Linking those tablets to the master file...
I know you can share a WB, not sure if that will help with this?

Regarding permissions for the "master", unless they need to see what that contains, why would then need access? If they just need to see what is in there, set it up as password protected/read only

 

[anneparker629]

Thank you, Ford! I guess I had better figure out how to use VBA.

And yes, each user needs to be able to view the contents of the Master file; that is the sole reason we are creating the workbook to begin with. But I will make sure to set it as Read Only.

 

[Meggesto]

I'm not sure excel is the best solution to this problem. This seems to be close to a Networking / Databasing scenario. However, it can be a solution though with the right work around. Everything you want to do can be done I think the problem is that if you want only one copy of the file multiple people can't be working on it at the same time in real time. Inevitable a dozen people cannot save the same file they will be writing over each others when saves occur. Maybe each individual could have their own personal worksheet to input on then you could write code in VBA to open those individual sheets at the days end once everything has been saved no longer in use that would pull data from each of their files and update the master. First you'd want to look into creating a Userform. This is the Fillable-Form-Version that you're looking for. You can custom create it so it can have fields for any bit of information you'd like, you can create a button to simply press in the worksheet that will pull up the userform and the user can simply input the information they need. Through VBA you can assign where that information ends up on the spreadsheet. You can write code that will save the worksheet after a change is made, upon closing, or just periodically. At the end of the day when each individual is no longer inputting information you could run a macro from the master sheet to dump all of that data into the master sheet. You'd password protect that sheet so that it would be Read Only and no one would be able to manipulate it. You could protect each individuals worksheet as well so no one accidentally makes changes.

 

[anneparker629]

YES! Thank you Meggesto, this is EXACTLY what I need to do!! I had the same thought as you about having individual sheets for each user that populates from their fillable-form, and then compiling the data from each user at the end of the day to save in the Master copy. Then, I would re-save the Read-Only version of the Master copy to each of their tablets for them to reference for the next day's work.

Would you be able to provide any tips or guidelines as to how to set up this Userform and then use VBA? I have already created a fillable form in Excel, but I just did that by inserting drop-down menus...I get the feeling that a Userform is different? Also, I've never used VBA, but I am not opposed to putting in the research and learning as I go.

Thank you again, this is precisely the kind of information I was looking for!

 

[Meggesto]

So if you've already managed to create a usable template for the field operators using drop down lists; and those lists can cover all the different inputs you'll need then you actually won't need a Userform. A Userform is essential a customized input box to help those who may not have experience in Excel input data correctly. It is also useful is the individual inputting the data isn't familiar with the types of data they're using so it can give them an idea of the range of values or types of required answers. I think that you may not need VBA for this project then. If you have the field operator template compiled then next step would be creating as many copies of this template as need be. I would then assign them names by their respective operator, or area, and maybe a date stamp (either month or month & year). Then I'd have the Master Workbook with a cover sheet that summaries any information that would like that can be taken from the field operators reports. Each tab in the Master Workbook following the Summary would be each individual field operators report. At the end of each day the field operator would need to save their reports and someone would then be in charge of compiling those reports into the Master Workbook (I.E. Copy and Paste the new information for each day). The Summary sheet could easily be designed to pull in any required information you'd like and update each time a report is updated in the Master Workbook. You really wouldn't need VBA to accomplish any of this actually. It could be handy possibly to save time compiling the reports but for a small amount of field operators it shouldn't be too difficult. If had a few dozen it would probably become necessary but I don't think Excel would be the best solution at that point. And so each morning your field operator would have their running list and the copy of the Master to reference. The Master should be locked to not accidentally be overwritten and possibly lock each field operators so only they can overwrite their own work. The next think you may want to think about is how often to save a backup of the Masterwork book and/or field operator logs and possibly how often you may need to purge them. I think between a little smart designing, correct usage of Excel formulas and linking cells, and a little bit of time this could be pretty simple and achievable without the need for VBA.

 

[anneparker629]

Meggesto, thank you so much for your thoughts and suggestions. I believe I have essentially created exactly what you have described and it seems to be working well.

And I have to admit...I am a bit relieved to see that you don't think VBA will be required...haha I'm not easily intimidated but I just can't seem to be able to get my head around it!