Hi,
I am looking for a way to recalculate say 500 cells in TM1 worksheet (slices) rather than whole worksheet. Also, F2+Enter works only on 1 cell. Doing this 500 times is not feasible. Any suggestions?
Recalculate specific cells ather than whole Worksheet
-
- Posts: 8
- Joined: Wed Sep 18, 2019 2:47 pm
- OLAP Product: Planning Analytics
- Version: PA 2.0.6
- Excel Version: 2013
- gtonkin
- MVP
- Posts: 1198
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Recalculate specific cells ather than whole Worksheet
Not sure of your exact application but I have a macro bound to Alt+R that executes Selection.Calculate
Highlight a range, press Alt+R and it gets updated.
Highlight a range, press Alt+R and it gets updated.
-
- Posts: 8
- Joined: Wed Sep 18, 2019 2:47 pm
- OLAP Product: Planning Analytics
- Version: PA 2.0.6
- Excel Version: 2013
Re: Recalculate specific cells ather than whole Worksheet
Is it something that can be inbuilt in the excel addin itself which can be used by every employee of the company rather than just a adhoc Macro file. Something in TM1 config files or so?
- gtonkin
- MVP
- Posts: 1198
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Recalculate specific cells ather than whole Worksheet
I use it separately. If you want to build it in, you would need to add the code to each template and Macro-enable your templates (save as .xlsm).
If you are using TM1 Web, macros are not supported.
If you are using TM1 Web, macros are not supported.
-
- Posts: 8
- Joined: Wed Sep 18, 2019 2:47 pm
- OLAP Product: Planning Analytics
- Version: PA 2.0.6
- Excel Version: 2013
Re: Recalculate specific cells ather than whole Worksheet
I am using TM1 perspectives not web. files are already heavy that is why need a solution that it calculates only specific cell, don't you think macro enabled files (xlsm) would make it more heavy?
Also, in the meantime, are you able to provide the vba script for it please?
Also, in the meantime, are you able to provide the vba script for it please?
- gtonkin
- MVP
- Posts: 1198
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Recalculate specific cells ather than whole Worksheet
Macro-enabling should not add more overhead unless you intend to add a lot of macro code-even then, this should not impact size too much.
Add a module to your workbook first. The code is simple:
Bind a short-cut key to the macro after you have created it.
Add a module to your workbook first. The code is simple:
Code: Select all
Sub CalculateSelection()
Selection.Calculate
End Sub
-
- Posts: 8
- Joined: Wed Sep 18, 2019 2:47 pm
- OLAP Product: Planning Analytics
- Version: PA 2.0.6
- Excel Version: 2013
Re: Recalculate specific cells ather than whole Worksheet
Thankyou so much.
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Recalculate specific cells ather than whole Worksheet
This is how it started for me in VBA and a couple of years later you have an add-in (xlam file) with 18,000 lines of codinggtonkin wrote: ↑Thu Sep 19, 2019 8:35 amMacro-enabling should not add more overhead unless you intend to add a lot of macro code-even then, this should not impact size too much.
Add a module to your workbook first. The code is simple:Bind a short-cut key to the macro after you have created it.Code: Select all
Sub CalculateSelection() Selection.Calculate End Sub
To automate Excel and TM1 tasks. Icons in the ribbon were added, but you could also use the QAT (Quick Access Toolbar) if you only have a handful of macros. Or you could create 1 macro, put it in the QAT, and pressing the icon would show a userform that lists the available macros/functionality that you or the user can pick from and launch from there. Shortcut keys are quick and useful too and don't require an icon in ribbon or QAT.
Just to show that automation can bring us many benefits, if we are prepared to code the whole thing (and sometimes use Google for code).
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly