Template Solution Needed

**JAMIAM** · 06-24-2016, 06:49 PM

At work I use excel to create a client profile document that gets printed before client meetings. After the meeting, an admin will enter that data back into the Excel file from handwritten notes. There is an individual file for each client, which poses problems because sometimes the format of this document needs to be updated. When this happens, we have to reenter all the info on the newly formatted spreadsheet. What is the best solution so that we can enter new info on the spreadsheet, and when we update the format that info doesn't need to be reentered?

I considered entering all the data on a separate worksheet and filling the formatted template from that, but I think it would be difficult and time consuming for the admins who enter the data to identify where stuff goes. So ideally, the data would be entered in the formatted cells.

Another solution I considered was to use named ranges for each piece of info, then when the template changes use a macro to move all values in named ranges to another workbook, then populate the new template based on info in that workbook...but alas...I'm not that good with VBA.

Any guidance would be greatly appreciated, even if it's advice on how to do this without Excel.

**dflak** · 06-25-2016, 10:50 PM

Databases are a lot more flexible at storing data and manipulating it than Excel is. The most important part of database development is the design of the tables and the relationships. This depends on what you want to track and how you intend to use it. Databases and Excel play very well together. You can build an Excel template that imports data from the database, passing the customer ID as a parameter and formats it into whatever reports you need. Depending on the design of the database, you can make the data entry forms mimic the excel spreadsheets for ease of data entry.

One of the perceived obstacles to using MS-Access (and this does sound like a good application for Access) is the perceived price. You do not have to buy a license for everyone. Only the developers need a paid license. All other users can download free, runtime-only licenses from Microsoft. These people will only be able to do data entry and run reports and queries already developed by the developers. They won't be able to change the database structure or create forms, etc. which you probably don't want them doing anyway.

The other objection to MS-Access is the perceived difficulty working with it as opposed to Excel. With good database and form design, you can make the learning curve very shallow.

P.S. Having said all this, if the database is really simple, you can use a "flat file" approach using Excel. Again the raw data is stored in one place and imported and sorted out by the template at runtime.

We'd really have to see the kind of data you keep for the reports to offer better advice. A sample report with non-sensitive data would help a lot.

**FDibbins** · 06-26-2016, 12:18 AM

I see a very common mistake here. You - and many others - have designed your sheet around what you want the output to look like, rather than designing it for data entry. The output format can come later, but the primary concern is the data capture side.

I would probably have 1 sheet to capture ALL data about ALL clients, and then use 1 or more sheets to pull summaries for whichever client you want. Doing it this way, your output format will not interfere with the data input at all

**JAMIAM** · 06-27-2016, 12:42 PM

Thanks for the reply!! I actually opened up Access and watched a few vids just before posting this, but it was hard for me to get the hang of in the span of an hour, so I'll dedicate some real time to it today to see if I can figure it out.

As FDibbins mentioned, the issue really is that we already have these spreadsheets we've been using so I was trying to force a process to work around the system that is already in place. Further complicating the situation, we use Redtail CRM, which hosts a lot of this information already and should host ALL of the information from these spreadsheets, but it's export feature is very limited and unable to get all the info we need. Ideally the admins would only have to enter the data in one place and it would look similar to the aesthetic of the spreadsheet for ease of use. Hopefully Access can achieve this.

This is my first post here, so I'm not sure how to share a file now that it's posted but here's a link to it that I hope works: https://www.dropbox.com/s/fy2yzztcm0...file.xlsx?dl=0

Thanks again!!!

**JAMIAM** · 06-27-2016, 12:50 PM

FDibbins, you're 100% correct about the issue of us focusing on what it was to look like once printed. We didn't perceive we'd be making so many changes and need it to have some kind of underlying "database" so that the template could change. I considered using a single workbook to hold ALL the client data as you suggested. My concern is that after the meetings where this data is collected on the formatted sheet, the admins would have to enter the info into the consolidated single file, which would be difficult and time consuming since they're not experienced with Excel and the format would be difficult to translate. Ideally, they would enter it on the formatted template, and I assume I'd need to write a Macro to get the info onto the consolidated single sheet, which I tried with no success. I'm very inexperienced with Macros, but had a little luck modifying code on another project, so I figured I'd give it a shot. No luck. I posted a separate post in the VBA section to see if someone might be able to help.