PAfE not calculating and returning #VALUE!
Posted: 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:
Hope this helps others trouble-shooting similar issues.
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