Skip to Content

I noticed that one of the unanswered frequently asked question on the SCN forum regards the use of Excel formulas in Input Schedules.

There is an option “Keep formula on Data”, it helps if only refresh occurs, it is good for static reports without dynamic expansions, but in real life in most cases there is at least one expansion per report, so this option is useless.

I wonder why this functionality is not documented (at least I could not find it), and in general I do not think that EPM add-in is well-documented, especially the part about formatting (using of EPM Formatting Sheet).

Step 1.

Create proper Excel formula inside your report, use upper left cell, here you are able to use absolute or relative referencing.

/wp-content/uploads/2013/01/formula2_180028.jpg

Step 2.

Go to the EPMFormattingSheet->Dimension Member/Property Formatting section – > Column

     2.1 Select “Apply” check-box on “Formatting on Specific Member/Property”

     2.2 Specify members (Budge and Forecast)

     2.3 Copy the resulting (on Step 1) formula to “Data” column, but in this case use apostrophe before the formula: ‘=C10*1,2

     2.4 Select or enter manually “Content” in “Use” column

/wp-content/uploads/2013/01/formula3_180107.jpg

Step 3.

Use this EPM Formatting Sheet for your report:

     3.1 Edit Report -> Options -> Check Apply Dynamic Formatting

     3.2 Select your EPM Formatting Sheet

Step 4.

Refresh Worksheet

During the refresh EPM add-in automatically adjusts the cell reference to refer to different cells relative to the position of the formula, as well as Excel does.

/wp-content/uploads/2013/01/formula4_180096.jpg

See the sample file if you need more details:

https://docs.google.com/file/d/0B2BL9pDBf4XfaHNVa1dnSVpyR0k/edit

Note: Even if you use localized version of Excel,  still on EPM Formatting Sheet you should write Excel functions in English. During the refresh it will be translated to your local language. I do not consider it as a bug, as for me It was really stupid Microsoft’s Idea to translate function names : )

Best Regards,
  Sergey

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Christina Schmidt

    Hi Sergey,

    thank you very much for this very good how to guide!

    Is it also possible to have more complex formulas, e.g. VLOOKUP formulas in the formatting sheet? Because I cannot get it to work with VLOOKUP…

    Regards,

    Christina

    (0) 
  2. Yigitalp Yalcin

    Hi Sergey,

    Thank you very much for this guide.

    I would like to ask if its possible to apply this method for two reports at the same time. I have two report in a specific sheet and this method only calculate the first report.
    How can i use it for both of them?

    PS: I have tired to have two different formating sheet for both but it didnt work.

    Thank you in adnvance,

    (0) 

Leave a Reply