I'm about to start a project that, if the pilot takes off, will create a lot of text data that I'll want to analyze using Excel functions. I initially thought to create a userform for easy entry and data standardization (I'll be entering the data manually at first, but the function may be outsourced to one or many people) and was pretty upset to find that functionality doesn't exist for Excel for Mac 2016. We don't have a database or any technology more advanced than a Macbook Air and Office for Mac.
Other than, you know, convincing my boss to buy me a PC, where do I even start?
Ideally I'd like what I create to have the following functionality:
- standardized method of data entry to enable entering small amounts of data quickly
- automated ID codes assigned through the data entry method - I'll want two components (the client number and the case number) in something like XXXYYY format. Projected number of clients for this project is ~200 by end of 2018, at which point I hope we have a more sophisticated tech function/custom-build database that can handle this data.
- ability to expand as the analysis and project get more sophisticated. At first, I'll only be tracking 5-10 fields for each case, but that could get bigger quickly
- ability to link the running total with a separate "database" (basically a gigantic interconnected series of tables and pivots that feed into my dashboards)
Which is not a lot, I know, but I'm at a loss where to start without the userform functionality. Do I:
- Just create an Excel table for each client with automated formulas for the ID section, etc., a master tab for ID-generating rules/client code lookup, and drop-down lists for certain entry points? This is easy for me to do, but I'm worried it will get clunky very quickly.
- Learn code to create a custom VBA for my needs? If so, where do I even start learning something like this? And since creating something from scratch will take me a while, how do I set up the initial Excel data storage to enable easy conversion down the road?
- Throw out Excel entirely and code a SQL database in Access? If so, any good resources like this for getting started in Access? I've done it once before, but incorrectly, and it was a nightmare.
- Do something else I haven't even thought of?
Any thoughts y'all have on the matter are greatly appreciated.
Most projects do not require complicated Userforms and the data entry screen can be created on a worksheet. Do the validations on the worksheet itself so that only a minimal VBA code is required. Such project can be up and running in a very short time (within a day, in my case) and the template can be reuse for other projects (that is why it can be implemented so fast). User with even a little knowledge of VBA can modify the project itself to suit their requirement. Additional fields can be added and the VBA codes can be amend easily. The data can then be linked to PivotTable/Dashboard for analysis.
I normally prefer all data to be stored in a single worksheet as it is easier to work with.
This is just my opinion.
I have attached a file for anyone who is interested to see how it is done. No idea if it will work on a Mac though.
Hi Alexandra,
VBA support for Excel 2016 Mac version is worse than the previous release, unfortunatelly.
A workaround is to design the user form on a Windows computer, it will work on Mac too.
usually, it's a bad idea to split the data into a galactic number of sheets, the data should be in a single sheet, with client reference as an attribute in a column instead of multiple sheets. This way, your life with excel will be easier: less complicated formulas, no need to include new sheets in existing reports, and so on.
As SunnyKow already mentioned, using one sheet designed for data entry is a better way than using a form. Data validation is almost inexistent in forms, you will have to write code for each validation you want. In sheets, data validation if far more stronger than in vba form fields.
If you need more help, you can upload your form and details, we will help you design the application.
