Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

How To Identify User Edits (Shared Formula Overwrites)

Adaptive Reporting is a great function for summarizing information that is calculated in sheets. In addition, it can be a powerful tool for investigating discrepancies in formulas due to a user manually overwriting a formula on the sheet.

Question

What happens when a user overwrites a shared formula directly on the cell and how do I identify the users change?

As an example, a formula for Net Income may be:

ACCT.4000_Revenue - ACCT.5500_Cost_Of_Goods_Sold - ACCT.6000_Expenses_7000_Allocations + ACCT.8000_NonControlling_Interest + ACCT.5100_Non_Operating_Income

If a user overwrites this formula and enters a value directly on the sheet in one level, it will cause the Net Income account to be different on the top level. However, this will not change the values in the accounts that make up the Net Income formula, such as the revenue and expense accounts. This will ultimately make the Net Income un-equal to the sum of the accounts in the formula on the top level.

Answer

If an account has a shared formula (that is, the formula is the same on all sheets), one way to do this would be to go to the Formulas page and select the desired account. The red sheet symbol represents where a formula has been changed on the sheet to represent another formula or value.

Although the formula page is the fastest way to check for user edits, it may not be helpful if the formula is not a shared formula (that is, the formula was made directly on the sheet). For this, you can create a report to visualize each of the values on one page.

To build your test report, you will want to create an extended organization structure on the horizontal axis, and the accounts in question on the vertical axis. You can then create a custom calculation next to the account to match what the correct formula should be. When you run the report, it will evaluate both the account and the correct formula by level, which can help trace the level in which this was changed.

Please see the video attached below, which shows how to do this in further detail. Note that each of the values in bold represent the roll-up levels, which cannot be manually modified. Thus, when looking through the report, only the non-bold values will be significant.

  • Was this article helpful?