IBP Excel Add-In — IBPFormattingSheet artefacts / formatting of outdated cells without data

[vincentverheyen]

As you can see, in this Planning View, there is only 1 time period (column N). The formatting of the IBPFormattingSheet is applied correctly there.

However, seconds ago, the end-user had changed the Planning View to multiple periods (which were in columns N, O, P and beyond). Now, the end-user changed back to 1 time period (column N).

Unfortunately, now in columns O and P, the old formatting is still applied. How can we change the formatting of these cells automatically?


The same happens when you change the Planning View by adding more rows (typically Key Figures). After switching back to less rows, for example by filtering, the old formatting remains at the bottom, even though it is unnecessary and clutters the view.

How can the user or configuration restrict the formatting to only those cells which have a match of column and row, or how can we clean up these formatting artefacts of unused cells?

[rahulmohnot]

Hi Vincent,

First try with marking the "Clear planning view format before applying Dynamic Formatting" within the Sheet Options to fix this issue.

Otherwise use the complex approach, which i used.

I used VBA hooks to resolve it.

Using "BEFORE_REFRESH ()", I initialized the complete format to normal. then once the data was refreshed the took the relevant formats.

Above that, you can use VBA hooks provided by SAP IBP for Excel add-in, to hide the unwanted rows and columns from the view.

-Rahul

[vincentverheyen]

Hi mohnotrahul. Thank you for your helpful answer. I wonder why one needs to resort to "BEFORE_REFRESH()"? Does the "Clear planning view format before applying Dynamic Formatting" not work as desired?

[vincentverheyen]

Hi mohnotrahul could you please update the answer, so as to elaborate a bit on the exact implementation, for those less familiar with VBA hooks?

[rahulmohnot]

Hi, I had used the BEFORE_REFRESH() to unhide the columns and rows which were hidden and AFTER_REFRESH() to hide the rows for cosmetic look of the view.