Hi John,
I have seen this problem many many times over the years - it is extremely common. By default, Excel will typically try to run (update / autocalculate) an Excel spreadsheet when it is opened (especially from an email).
The solution is very simple - before the Controller end user saves their Excel spreadsheet, they should untick the box which makes it autocalculate when opening
=> Then when the non-Controller user opens the Excel spreadsheet (typically from an email attachment) it will not try to run/refresh/update itself.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specifically, the Controller end user must click "File - Options" - "Formulas" - then:
- change "Workbook Calculation" setting from 'Automatic' to 'Manual'
- Untick "Recalculate workbook before saving"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
See "
Method #1" here for more details:
https://www.ibm.com/support/pages/excel-cell-values-change-name-or-zeros-when-spreadsheet-xlsx-or-xls-file-opened@Natascja - Lovely to hear from you - hope you are well! :-)
Good luck!
-Richard.
------------------------------
Richard Collins
------------------------------
Original Message:
Sent: Sun June 27, 2021 02:21 AM
From: Natascja Barsanti
Subject: Share Cognos controller updated Excel workbook.
Hi John,
Probably a bit late, what we do in our Excel reports is we have a data sheet that pulls back all the data from Cognos Controller (with the fgetval formula) and then we have another sheet called data values in the Excel report which is an exact replica of the data sheet but created using paste values. All the formulas in our Excel reports link to the data values sheet. A user will refresh the Excel pack and run a macro to paste values. The Excel pack is then saved and can be viewed by anyone regardless of whether they have access to Cognos Controller as although the data sheet goes to #VALUE! the data value sheet doesn't and all tabs within the report are linked to the data values sheet.
Thanks
Natascja
------------------------------
Natascja Barsanti
Original Message:
Sent: Thu March 21, 2019 04:37 AM
From: John Friberg
Subject: Share Cognos controller updated Excel workbook.
Hi
Our financial manager is responsible to update the company packages with Cognos Controller, when this is done, he often needs to share it with our Financial director.
The problem is that when our CFO opens Excel it autoruns the formulas and all the content change to #Value! as he doesn't have access to Cognos Controller.
How can we fix that?
Thanks in advance
John
------------------------------
John Friberg
------------------------------
#CognosController