Categories > OpenTBS with XLSX >

Formulas not updating after merge

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Iain
Date: 2015-02-19
Time: 15:18

Formulas not updating after merge

Hi, I have a spreadsheet template which has a single template row; this template row is then used by my database query to fill around 800 rows of data in the sheet.

I then have some formulas and pivot tables in other worksheets in the template which perform calculations based on the content of the data sheet - for example:

=COUNTIF('Member data'!J:J,"Y")

So, it should show the number of records in the data sheet where the content of column J is "Y".

The pivot tables work fine when the spreadsheet is opened after the merge, but the formula above still shows the same as it showed in the template i.e. 0. If I type the same formula in another cell, it calculates it correctly, but the original formula is still showing 0.

I've seen the comments in the XLSX template, but they don't seem to explain why data added in that column would not get included in this formula.

Apologies if I'm missing the glaringly obvious, but I can't see a way round this right now.

Thanks, Iain
By: Skrol29
Date: 2015-02-23
Time: 15:03

Re: Formulas not updating after merge

It seems that your workbook is not fully updated when it is loaded.
Can you try the flowing code (insert anywhere between LoadTemplate() and Show(), preferably just before Show()).

It it works, the feature will be added in the next version.

$TBS->PlugIn(OPENTBS_SELECT_FILE, 'xl/workbook.xml');
$e = clsTbsXmlLoc::FindStartTag($TBS->Source, 'calcPr', 0);
if ($e === false) {
    $p = strpos($TBS->Source, '</workbook>');
    $TBS->Source = substr_replace($TBS->Source, '<calcPr fullCalcOnLoad="1"/>', $p, 0);
} else {
    $e->ReplaceAtt('fullCalcOnLoad', '1', true);
}
By: Iain
Date: 2015-02-24
Time: 13:41

Re: Formulas not updating after merge

Thanks for that - I added thad code and it worked fine now, thanks a lot!

Will I need to remove that code once you include it in your plugin? Or will it not matter if the same code runs twice?

Any ideas on timescales for the next version? No rush as it works for me now, just wondering to look out for it.

Cheers!
By: Skrol29
Date: 2015-02-24
Time: 13:58

Re: Formulas not updating after merge

Thanks for this feed back.

> Will I need to remove that code once you include it in your plugin? Or will it not matter if the same code runs twice?

It will be compatible : no matter if the same code run twice.

> Any ideas on timescales for the next version? No rush as it works for me now, just wondering to look out for it.

In 1 or 2 weeks a new version with minor new features will be released.
By: Iain
Date: 2015-02-24
Time: 14:00

Re: Formulas not updating after merge

Thanks, that's good to know.

Also, I couldn't see any changelog on new versions - I don't know if I missed it, but if not, it'd be good to have a changelog available on them to help know if we need to pick up a new version.
By: Skrol29
Date: 2015-02-24
Time: 14:36

Re: Formulas not updating after merge

The change log is given at the end of the documentation. (I will add a link on the OpenTBS page.)
It is also send by email with the newsletter.
By: Iain
Date: 2015-02-24
Time: 15:47

Re: Formulas not updating after merge

OK that's good, thanks for the info.

And thanks for your work on this plugin, it made a huge difference to a reporting app I'm building!