PAfE not calculating and returning #VALUE!

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
gtonkin
MVP
Posts: 820
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

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
        ws.Activate
        rngInvalid.Select
        Exit Sub
    End If
Next

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

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

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

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: http://www.cpearson.com/excel/findall.aspx
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
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Post Reply