Help/ Ideas needed - Excel/VBA Based Reporting tool

[ProjectThree]

Hi All,

I'm looking for some inspiration and ideas regarding an excel based reporting tool for field workers. I currently envisage a worksheet formatted to look like a form, this will have elements such as cells using drop down and content validation, tables to record values etc. The format of the report can change a fair amount, and additional rows etc can be required on tables, there will be sections that may or may not be applicable. This then needs to be able to be exported as a PDF document.

The solution I'm currently looking at it a "Snippet library" with blocks of content on a worksheet, and a separate worksheet containing an index with the cell range addresses for the snippets. I'll then have presets of standard reports, or pages which can be selected, a macro would then run through the list of required snippets and then run down the index list, find the location on the snippet library sheet and then copy it into a report worksheet in sequence. The snippets will already have buttons and associated macros to add additional blank lines to tables etc.

There would also be an index sheet / list which tracks the snippets used, start and end points. When additional lines are added, this index would be updated so that it should remain accurate. This would also allow for additional snippets to be "sandwiched" between existing ones.

I was then planning on using just a standard print to PDF with a defined print area to export to the required format.

This however, will take a long time to develop, and I'm not entirely sure that this will work. Does anyone have any other ideas? done anything similar? We're looking to avoid buying into an existing solution due to a low cost solution being required, however I am open to other open source software or solutions available under the Microsoft E3 license.

 

[Rijnsent]

Hi ProjectThree,

the solution you're describing sounds indeed low cost, but will cost quite a bit of time for building and maintaining. On the other hand: excel is a great tool to develop something quickly. I generally go for the following style of workbook:
-One welcome sheet with a small number of really important selections/choices for the user
-The main form(s). Here I normally hide blocks of lines/columns and unhide them when the user has to fill them in because he/she selected something
-A very hidden sheet with lists for dropdowns and/or choices the user made, this can also include things like your version number
-A very hidden sheet with the export data: if you want to process the forms in some way you probably need the data. Use this sheet to transform the data that the user filled into a more fixed format that can easily be picked up. Generally that would be a bit more database-like.

More generally: start small. It's better to get people enthusiastic with a small sheet that does something than a massive one that will forever be "under construction".
Hope those pointers help a bit,
Cheers,
Koen

 

[Logit]

.
I have oftentimes found if the employee is presently required to fill out an existing form that covers your goal, base your project on the existing form. Some changes
will most likely occur as the project develops.

If you don't presently have existing forms that are being utilized ... can you create a non-working worksheet that comprises what you visualize the project would look like ?

Everything you've outlined in your description is doable in Excel. From your description I would surmise you already have some ideas what the project would look like but
of course we don't.

Create something then post it on a cloud website so others can download and see what you are visualizing. DropBox.com or similar ... then post the link here so we
can see what you have visualized.

Thanks