7.21.0880 |
  
|
7.21.0880 |
|
Released on July 7, 2021
This is a temporary workaround, use this only if you really need it.
We have implemented support for large time-frame sheets (up to 240 periods) and cash-data sheets (up to 435 periods)
We did this because some customers need this right now to handle projects having up to 20 years duration. However, this is not recommended that you use this new feature if you don’t absolutely need such large number of months in your projects.
Notice that you can adapt your time-frames to use trimesters, quadrimesters or even semesters to reduce the number of periods in a time-frame. When you have less periods, computing of the estimate is faster.
We have a major limitation in QDV: the component we use for the spreadsheets can only handle XLS format when we need controls and comments. It can export to XLSX format but then, we lose controls and comments. XLS format supports up to 256 columns while XLSX format has up to 16384 columns.
So, only because some customers need this urgently, we implemented a “dirty” workaround: we store both XLS (255 columns with controls and comments) and XLSX (16384 columns without controls and comments) into QDV files each time the overhead workbook is saved.
At loading time, we open the XLS file as usual, we copy and paste large sheets from the XLSX file and we adapt the references so that sheets from the XLSX files belong to the workbook.
This works well but this is quite slow, particularly the adaptation of references which is invoked each time we read or write the overhead workbook to the disk.
For this reason, this extension is not activated by default. Is can be activated using a check box in the calculation options of the estimate. You should never activate this option if you don’t need it.
Checking this box will bring some limitations:
1.You can no longer directly import the overhead workbook from Excel, you’ll have to use the ‘import settings’ function for this
2.You can still edit under Excel but it’s significantly slower
3.You cannot reference a cell beyond column IV (256) directly in your formulas, you’ll have to use INDIRECT(). Usually you don't have to reference such columns from Time-Frame and Cash-Data sheets.
4.You cannot import the overhead workbook of an estimate having the option activated into an estimate not having this option activated
5.Opening and saving estimates is slower because we have to load and save both formats and we have to mix them.
6.The report generator doesn’t support large worksheets yet so you lose columns beyond column IV (256) in all overhead reports
7.You cannot get large sheets (more than 256 columns) with their controls and in-cell comments (see below).
We have added a new question when you attempt to export the overhead workbook to Excel in the new context (with the extended time-frame and cash-data option activated): You’re prompted to chose between XLS and XLSX formats.
With the XLS format (as before), you get only the first 256 columns of each sheet so when your time-frame sheets or cash-data sheets have more columns, you don’t get them into Excel. But you keep all controls (list boxes, check boxes, etc.) and all in-cell comments.
With the XLSX format, you get all columns but controls and comments are lost.
QDV is not able yet to provide all of this in the same workbook.
Notice that, by design, you cannot display more than 1000 columns in the minutes and nomenclatures views. It was almost impossible to have 1000 columns with limited time-frames but now, with such large time-frames, you could have more if you insert the factors, the times, the costs, etc.
If you do this QDV may crash wit unclear message. Fortunately, no one will really need 1000 columns at once in the minutes!
Because we didn’t want to create a new version of the QDV files (that would penalize all users, including users who don’t need such large time-frames), we kept the same index of files even if an overhead workbook having more than 255 columns is not compatible with older versions of QDV. So, when you import an overhead workbook created with the new version of QDV having the new option activated under an old version of QDV, you just have a warning message saying that the time-frame sheets or the cash-data sheets cannot be calculated and you’ll have to reload a correct overhead workbook.
All these problems will be solved when the spreadsheet component will be extended to fully support the XLSX format (with controls and comments). This is expected soon (this year or in the year 2022). When we have this, we’ll remove this “dirty” workaround and create a new version of QDV files so that all users can enjoy worksheets larger than 256 columns. This will also significantly improve loading, saving and exchanges with Excel.