Add additional columns to spreadsheet based on template

Question

Is there a way to add some predefined analysis/evaluation cells to an existing spreadsheet?

My use-case is an application that generates an MS Excel file with employee data from a database. We need to evaluate this data in ways that are not possible from within the application, so we add some columns with formulas in Excel.

Is there a good way to save these evaluation cells as a template and apply them to a newly generated file?

Example file/table that is generated from application:

+-------------+
| ColA | ColB |
+-------------+
|  foo | baz  |
|  bar | baz  |
|  foo |      |
|  bar |      |
+-------------+

With additional formulas that should be added automatically (currently, I use copy/paste to add the formulas from an old file)

+-----------------------------+
| ColA | ColB | Eval1 | Eval2 |
+-----------------------------+
|  foo | baz  |   1   |   1   |
|  bar | baz  |   0   |   1   |
|  foo |      |   1   |   0   |
|  bar |      |   0   |   0   |
+-----------------------------+

Edit to clarify: I do not want any explicit formula, but a more general way on how to add arbitrary columns with formulas. I'm very new to Excel (and have not touched VBA so far), so I don't know if this is even possible and how much work is involved.

This question is not about any calculation, but more about the process of adding custom predefined formulas to newly generated .xlsx files. The before and after tables in my example are only to demonstrate where columns should be added, but are not meant to carry any semantic.

For my explicit problem, I always get a file with the columns Month, Employee and WorkHours. Then, the columns with the percentages need to be added, which I usually do by hand and drag them down for all the rows. This is not a complicated task, but I need a way to make it accessible to my coworkers without having them manually copy the formulas from a file somewhere.

+-----------------------------------------------------------+
|Month    Employee   WorkHours    25%    50%    75%    100% |
|01.2016  PersonA       149,25    FormA  FormB  FormC  FormD|
|01.2016  PersonB        79,00    FormA  FormB  FormC  FormD|
+-----------------------------------------------------------+

Formulas are the following:

FormA: =IF(AND(C2>0;C2<=40);0,25;"")
FormB: =IF(AND(C2>40;C2<=80);0,5;"")
FormC: =IF(AND(C2>80;C2<=120);0,75;"")
FormD: =IF(C2>120;1;"")

score 2 · Accepted Answer · edited Mar 20 '17 at 10:04

Certainly you can use VBA routine(s) to automate most activities that can be done manually (and can be objectively described). A couple of broad approaches come to mind:

Build on your current approach of having a template file with the predefined formulas. Add VBA to that file to automate what you’re doing now: copy the formulas from the template into the generated file. This may be the better (easier) approach, especially if you expect to change the analysis/evaluation formulas — and especially if you need to allow your co-workers to modify the template.
Create a new Excel file that contains only VBA. Embed the desired Excel worksheet formulas into the VBA as data. This would automate the action of typing the formulas into the new sheet. This approach might facilitate tailoring the formulas to the existing data.

Of course, it’s not either/or; you can devise a hybrid solution that combines aspects of the above.

You should see How do I add VBA in MS Office? for general information. That describes how to activate the “Developer” tab on your ribbon. If you don’t know VBA, a good way to start is to click “Record Macro” on the “Developer” tab, choose a name and click “OK”, walk through the actions that you want to record/automate, and then click “Stop Recording” (which is the same button as “Record Macro”). I seem to recall that, ten or twenty years ago, macros were recorded and stored as sequences/scripts of keystrokes; now they are translated into the equivalent VBA.

You’ll probably want to edit the code to make it bulletproof. Teaching you VBA is beyond the scope of this site, but it occurred to me that you would want to dynamically “find” the worksheet where you want to put the canned formulas. Here’s an approach to doing that:

Set curw = ActiveWindow
Set curs = ActiveSheet
For Each w In Windows
    w.Activate
    For Each s In Sheets
        s.Activate
        If Range("A1") = "Month" And Range("B1") = "Employee" _
                                 And Range("C1") = "WorkHours" Then
            ' This is the sheet where I want to put the analysis/evaluation formulas.
            If Range("D1") = "" And Range("E1") = "" And Range("F1") = "" _
                                And Range("G1") = "" Then
                ' Put the formulas here.
            Else
                MsgBox "There’s already something here!"
            End If
        End If
    Next s
Next w
curw.Activate
curs.Activate

If you search this site, and other resources, you’ll find plenty of examples of how to determine how much data is already in the target sheet (so you can extend your new columns to match).

You can tell your co-workers to open the template file and run a macro to run the macro to install the formulas. You can create a button to save them the bother of needing to navigate menus. See also How to store VBA Macros for Office 2007 to have them always available and share them with others? for information on how to make the macro available to your co-workers without their even needing to open the template file.

Add additional columns to spreadsheet based on template

1 Answers1