1

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;"")
|improve this question
  • The question is kind of ambiguous. You show a before and after, but it isn't clear where the after comes from or how the values relate to the before data. I'm not sure how anybody could respond to this in a useful way. Can you elaborate and be more explicit? – fixer1234 Dec 20 '16 at 10:27
  • I added some clarification, hope it is better now. If you have any further questions or ideas how I can improve the answer, let me know. Thanks! – Slizzered Dec 20 '16 at 10:40
  • You might just be over-thinking this. It's like the Nike slogan, "Just do it." When you open the file in Excel you can add whatever you want, where you want. If you want to add a column with a particular calculation, for example, you stick the formula in the first cell where you want it, and copy or drag it down to populate the column. Maybe if you can think of an example of what you might want to do, people could provide a flavor of how that would be accomplished. – fixer1234 Dec 20 '16 at 10:51
  • 1
    I already did it the "just do it"-way, and everything is fine. But my co-workers have to repeat the process every month with a newly generated file. They would rather do all the calculations by hand instead of learning how to add the columns manually and waste 2-3 days in the process. Therefore, I was searching for a way to improve their workflow. I will add some real data to showcase the situation more clearly. – Slizzered Dec 20 '16 at 10:58
  • 1
    I see the task, but it still isn't clear what kind of input you're looking for. The impression I get is that you have some co-workers with little computer experience (or at least Excel experience), and they are unable or unwilling to learn what would be needed to be proficient at this task. If so, that isn't something people here can do much to help with. It would come down to maybe creating some macros to automate it, or somebody (you?), doing the technical part, or getting them some training, etc. – fixer1234 Dec 21 '16 at 4:34
2

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.

|improve this answer
  • That was a very complete answer, thanks. I ended up writing a VBA AddIn so that there is a Ribbon/Menu entry for the desired functionality. – Slizzered Jan 10 '17 at 15:42

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.