Affinity Publisher formula in tables

[Greyfox]

Yesterday I was working on a project in Affinity Publisher 1.9.2.1035 (on Windows 10 Pro) that required a simple table where one column needed to have a total cell showing the sum of the column values and I was surprised to find the the formula "=sum(B3:B20)" didn't work.  After doing some searching in the forum it appears that at least early in its life, Affinity Publisher tables did not have the ability to use any formula.

From my searches, I found references to AP being able to import Excel tables, but apparently cells with formula are imported only with the calculated value that was arrived at in Excel. If any cell values are subsequently changed in AP, the calculated cells will then show an incorrect value.

Coming from years of use of PagePlus this seems to be a significant step backwards, but perhaps things have changed since the posts I found.

Can someone please tell me whether there is a way to have formula work in tables in the current version of AP, and if not whether there is any known plans to add that functionality?

[h_d]

Affinity Publisher is a desktop publishing application, not a spreadsheet, and like most DTP apps with the possible exception of Apple Pages, it doesn't do spreadsheet calculations. Never has, still doesn't. Serif don't usually reveal future plans for their apps. But it clearly was a feature of PagePlus, so who knows...

[Greyfox]

1 hour ago, h_d said:

Affinity Publisher is a desktop publishing application, not a spreadsheet, and like most DTP apps with the possible exception of Apple Pages, it doesn't do spreadsheet calculations. Never has, still doesn't. Serif don't usually reveal future plans for their apps. But it clearly was a feature of PagePlus, so who knows...

OK, thanks for the response. I had hoped that there might have been something in that regard in a roadmap or in beta. Disappointing. For now, at least for this type of project, back to PagePlus X9.

[walt.farrell]

7 hours ago, Greyfox said:

For now, at least for this type of project, back to PagePlus X9.

Alternatively, you could just do the spreadsheet in a different application, such as LibreOffice Cals, export as a .xlsx file, and then put that file into your Publisher file as a table.

[Greyfox]

2 hours ago, walt.farrell said:

Alternatively, you could just do the spreadsheet in a different application, such as LibreOffice Cals, export as a .xlsx file, and then put that file into your Publisher file as a table.

I can do the spreadsheet tables in Excel since I have Office. That's not the point.

The particular application is one where from time to time one or more of the values in the tables has to be changed, resulting in different column totals.  In Pageplus X9 it's just a matter of updating the particular cells and the totals are automatically recalculated.

In Affinity Publisher it seems I would have to change the values in the cells, then either recalculate the totals manually, either with pen and paper, or with a calculator app, and manually enter them in the tables. That means having to enter all of the values in calculator, or on paper, not just the ones that have changed, and then manually updating the total in the table itself, increasing the possibility of error.

Another way would be by keeping the Excel files for all of the tables, updating them and then replacing the whole table in Affinity Publisher each time there is a change.  Aside from having to keep and manage a whole bunch of Excel files, that is way more work than just updating the values in PagePlus X9.

[walt.farrell]

1 hour ago, Greyfox said:

Another way would be by keeping the Excel files for all of the tables, updating them and then replacing the whole table in Affinity Publisher each time there is a change.  Aside from having to keep and manage a whole bunch of Excel files, that is way more work than just updating the values in PagePlus X9.

Yes, that is what I was suggesting. And yes, it's more work than if you stay in PagePlus X9, but many (most?) of us don't have the option of using PP X9.

[GarryP]

If you export the spreadsheet from Excel to PDF and then place – linked – the PDF into Publisher then every time you export the spreadsheet to PDF Publisher will automatically update the table, unless you have switched that option off in Preferences.

[Greyfox]

16 hours ago, walt.farrell said:

Yes, that is what I was suggesting. And yes, it's more work than if you stay in PagePlus X9, but many (most?) of us don't have the option of using PP X9.

I have hundreds of .ppp files. As you would be aware, Affinity Publisher doesn't support them, so I keep  PagePlus X9 installed so I can open them on an as needed basis and export as PDF so I can open in Affinity Publisher, so having to drop back to PagePlus for the odd job in my case isn't a problem.

[Greyfox]

33 minutes ago, GarryP said:

If you export the spreadsheet from Excel to PDF and then place – linked – the PDF into Publisher then every time you export the spreadsheet to PDF Publisher will automatically update the table, unless you have switched that option off in Preferences.

Thanks Gary. That would be an option for some applications

In this particular application there are a lot of individual, but similar, documents generated. Each document has one of a small number of custom format tables included. Each custom format table can be used with a number of documents that require the same cell references, but will have different values. So I have the custom format tables saved as assets. Once a table is pulled into the document, it is just a matter of filling in the values in one of the columns, and in PagePlus X9, those values are then automatically summed. At a later stage, after those values have been checked in the field, one or two may need to be updated.

In this particular instance it would be extra work and document management to use Excel tables when I can simply open the .ppp file, change the one or two values, have the total updated automatically and then export the document.  For this particular application it seems PagePlus X9 is the better tool to use, much as I now prefer Affinity Publisher for other work.

[missamae]

It seems that simple spreadsheet functions are highly requested with this software. I too was surprised this level of functionality was missing especially after understanding that it was possible with PagePlus. I also assumed that artistic freelancers were the majority of users for these programs. I thought that having the freedom to design an efficient invoice that doesn't look generated from a microsoft word template would be an option and a selling point.

I realize Affinity Publisher is a great and more accessible alternative to Illustrator (and the sorts) so this functionality isn't high priority, but I would think that this feature would put it ahead of competition. Maybe not, but perhaps a plug-in or an additional add-on would satisfy all parties.

Does anyone know of any other software aside from Apple Pages that can do this? I'm looking for options that help with efficiency because life is too short for workarounds lol

[paulo Dekkers]

Can the current version of Affinity Publisher perform calculations with data in a table? For instance, if I want to create an invoice or quote and need to quickly adjust the hours, resulting in a higher price for the invoice or quote. Does Affinity handle calculations like Excel, or do I need to make manual adjustments? 

Is this a feature that is in the Pipeline? it would be a very welcome addition to AF Publishers functionality.

[walt.farrell]

10 hours ago, paulo Dekkers said:

Can the current version of Affinity Publisher perform calculations with data in a table?

No.

10 hours ago, paulo Dekkers said:

Is this a feature that is in the Pipeline?

I'm sure it's been requested, but no one except Serif would be able to answer that question, and they usually will not provide an answer. 

If it's important to you, you can look in the Feedback for the Affinity V2 Suite of Products forum to find an existing request, and add your thoughts there. Or, if you don't find one, you could post your own topic about it.