Have you ever ran into a situation where a template formula was only applying to the first row of a report? Or the formula you set in design mode is not showing up in report or data entry mode?
This is probably happening because the column containing the formula is not within the data view of the report. By clicking on the cog wheel > Locate on the data view, you can confirm this by seeing that the template formula column is not within the blue highlighted area of the data view:
It use to happen to me all the time, but i started using blank in columns and rows, specially if you want the headings or special summarization / average / sub-total in between lines, blank rows can help immensely.
Really useful tip, was happening to us when we first set up our templates and then realised how to fix it. Also realised that we could link the comments column to a certain time line to ensure that we can still see previous months comments in a table comparing previous to current month.
My hot tip – you can stop worrying about having blank rows for sums if you use offset within your sum formula.
Example:
You have data in Column C that you want to sum for rows 6 through 12, but the number of rows might expand. Most folks make a blank row 13 and put the formula “=SUM(C6:13)” in cell C14.
INSTEAD, I do not make a blank row and in cell C13, I enter the formula “=SUM(OFFSET(C5,1,0):OFFSET(C13,-1,0)”
No, I will not get a circular reference error - the formula understands that the end point of the range is always one row above itself.
It use to happen to me all the time, but i started using blank in columns and rows, specially if you want the headings or special summarization / average / sub-total in between lines, blank rows can help immensely.