*Please note that you can click on the images to enlarge them*
The Excel add-in is one of BOARD’s most user-friendly features. It allows the end user to edit data and execute familiar formulas whilst still benefitting from the exceptional reporting and analysis capabilities that BOARD has to offer. Version 9 brought with it the release of an alternative to the Excel add-in in the form of BOARD’s own formula editing tool; Nexel, which allows users to perform cube-based calculations within a dataview.
The following issue I encountered within this blog is one that I stumbled upon whilst working with a client on some data entry inside the Excel add-in.
This issue can be experienced when using the Excel add-in to enter data into an Infocube in BOARD, whereby the difference between 0’s (zeroes) and ‘Blanks’ within Excel is not detected. Below you can see that I have generated a table of data in Excel using the popular ‘VLOOKUP’ function, with two subsequent tables that take this value and replace any errors with either blanks or zeroes.
In this example, my intentions are to take the data fetched by the ‘VLOOKUP’ function from the reference table and paste them into my BOARD table, which will then be saved back to my Infocube in BOARD. The next screenshot shows the data held in the ‘IF N/A, then “”’ table being copied and pasted into BOARD’s dataview, which has been highlighted in green. You can see the blank values towards the bottom, rather than zeroes.
If a user now attempts to save this data into their Infocube, with blanks included, the dataview will not only reject the blank cells, but it will reject ALL cells that are highlighted in green. Even if the user is copying and pasting a huge table (e.g. a 1000 by 1000 grid) and only one cell contains a blank entry, the entire dataview will be reset, which is shown in the screenshot below.
However, if I copy the data from the ‘IF N/A, then 0’ table, the data entry will be accepted by the dataview and successfully write back to the Infocube, which can be seen in the screenshot below. The issue is a result of the ‘IF N/A, then “”’ data attempting to post a null value into a numerical cube, which is accepted as data entry but cannot be written back into BOARD, causing the dataview to reset.
The simple and most effective way to avoid experiencing this issue yourself is to make sure any ‘else’ values that are returned as the result of a formula are zeroes, rather than blanks. Hopefully, having read this blog, any BOARD-Excel enthusiasts out there can continue to build outstanding reports using their favorite formulas without a worry!Back to blog