PAfE not calculating and returning #VALUE!

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Posts: 913
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL; PAoC
Excel Version: Office 365 64-bit
Location: JHB, South Africa

PAfE not calculating and returning #VALUE!

Post by gtonkin » Mon Oct 05, 2020 7:26 am

I had an issue recently with a workbooks containing three sheets: Cover, Dynamic Report, Reference sheet.

The Cover sheet has a SUBNM to solicit the relevant period for reporting.

The Reference sheet uses the period from the Cover sheet to derive the Fiscal Year Start and the next 23 periods, calendar period names, period type e.g. Actual, Budget, Forecast and the relevant scenario for the Budget and Forecast.

The Dynamic Report shows expenses for a 24 month period as well as YTD values for the scenario relevant to each period.

Everything was working well until it stopped working after some changes were made on the Dynamic Report.
Trying to find the problem proved a little tricky. Using the Refresh Sheet from the PAfE toolbar kept on returning #VALUE! in the cells on the Reference Sheet. I tried hashing out and recalculating bits and pieces to find where it was going wrong but to no avail.

I added an Action button to only recalculate the current sheet and this seemed to calculate the values as before, but not all of them. There were still one or two #VALUE! formulas.

To cut a long story short, I eventually tracked it down to the TM1RPTVIEW with a TM1RPTTITLE containing an invalid reference i.e. a #REF!
After correcting this, the Refresh Sheet worked again.

What is still confusing is that the Dynamic Report is dependent on the Reference sheet, why would a Refresh Sheet calculate anything on the other sheet AND perform a Dynamic Report rebuild (TM1REBUILDOPTION=0)?

Keep in mind too that #VALUE! could also be that your connection has mysteriously timed out or dropped - happens when you least expect!

I created a quick macro to find the #REF! in the future:

Code: Select all

Sub FindInvalidFormulas()
' Find formulas containing search string e.g. #REF! which may prevent PAfE from working
Dim rngInvalid As Range
Dim ws As Worksheet
Dim strSearch As String

strSearch = InputBox("Search string?", "Find Invalid Formulas", "#Ref!")

For Each ws In ActiveWorkbook.Sheets
    Set rngInvalid = ws.Cells.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not rngInvalid Is Nothing Then
        Exit Sub
    End If

MsgBox "No formulas found containing the search string.", vbOKOnly, "Find Invalid Formulas"

End Sub
Hope this helps others trouble-shooting similar issues.

Wim Gielis
Posts: 2690
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium

Re: PAfE not calculating and returning #VALUE!

Post by Wim Gielis » Mon Oct 05, 2020 9:25 am

Hi George,

Thanks. For this exercise, I tend to use code from the late Chip Pearson:
There is a procedure called 'FindAllOnWorksheets'. After that you can simply loop over any results returned.
It's good to add "xlFormulas2" in the Find procedure, though, like you showed above.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014 ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references

Post Reply