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.
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
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.
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
6 Comments
