Greyfox Posted July 11, 2021 Share Posted July 11, 2021 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? Quote Intel i7-10700 Gen10 CPU, 32GB RAM, Geforce GTX 1660 OC 6GB Windows 10 Pro 22H2, 1x 1TB M.2 NVMe, 1 x 2TB M.2 NVMe. Affinity APh, APu, ADe Link to comment Share on other sites More sharing options...
h_d Posted July 11, 2021 Share Posted July 11, 2021 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... Quote Affinity Photo 2.0.3, Affinity Designer 2.0.3, Affinity Publisher 2.0.3, Mac OSX 13, 2018 MacBook Pro 15" Intel. Link to comment Share on other sites More sharing options...
Greyfox Posted July 11, 2021 Author Share Posted July 11, 2021 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. Quote Intel i7-10700 Gen10 CPU, 32GB RAM, Geforce GTX 1660 OC 6GB Windows 10 Pro 22H2, 1x 1TB M.2 NVMe, 1 x 2TB M.2 NVMe. Affinity APh, APu, ADe Link to comment Share on other sites More sharing options...
walt.farrell Posted July 11, 2021 Share Posted July 11, 2021 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. Quote -- Walt Designer, Photo, and Publisher V1 and V2 at latest retail and beta releases PC: Desktop: Windows 11 Pro, version 23H2, 64GB memory, AMD Ryzen 9 5900 12-Core @ 3.00 GHz, NVIDIA GeForce RTX 3090 Laptop: Windows 11 Pro, version 23H2, 32GB memory, Intel Core i7-10750H @ 2.60GHz, Intel UHD Graphics Comet Lake GT2 and NVIDIA GeForce RTX 3070 Laptop GPU. iPad: iPad Pro M1, 12.9": iPadOS 17.4.1, Apple Pencil 2, Magic Keyboard Mac: 2023 M2 MacBook Air 15", 16GB memory, macOS Sonoma 14.4.1 Link to comment Share on other sites More sharing options...
Greyfox Posted July 11, 2021 Author Share Posted July 11, 2021 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. Quote Intel i7-10700 Gen10 CPU, 32GB RAM, Geforce GTX 1660 OC 6GB Windows 10 Pro 22H2, 1x 1TB M.2 NVMe, 1 x 2TB M.2 NVMe. Affinity APh, APu, ADe Link to comment Share on other sites More sharing options...
walt.farrell Posted July 11, 2021 Share Posted July 11, 2021 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. Quote -- Walt Designer, Photo, and Publisher V1 and V2 at latest retail and beta releases PC: Desktop: Windows 11 Pro, version 23H2, 64GB memory, AMD Ryzen 9 5900 12-Core @ 3.00 GHz, NVIDIA GeForce RTX 3090 Laptop: Windows 11 Pro, version 23H2, 32GB memory, Intel Core i7-10750H @ 2.60GHz, Intel UHD Graphics Comet Lake GT2 and NVIDIA GeForce RTX 3070 Laptop GPU. iPad: iPad Pro M1, 12.9": iPadOS 17.4.1, Apple Pencil 2, Magic Keyboard Mac: 2023 M2 MacBook Air 15", 16GB memory, macOS Sonoma 14.4.1 Link to comment Share on other sites More sharing options...
GarryP Posted July 12, 2021 Share Posted July 12, 2021 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. Quote Link to comment Share on other sites More sharing options...
Greyfox Posted July 12, 2021 Author Share Posted July 12, 2021 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. Quote Intel i7-10700 Gen10 CPU, 32GB RAM, Geforce GTX 1660 OC 6GB Windows 10 Pro 22H2, 1x 1TB M.2 NVMe, 1 x 2TB M.2 NVMe. Affinity APh, APu, ADe Link to comment Share on other sites More sharing options...
Greyfox Posted July 12, 2021 Author Share Posted July 12, 2021 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. Quote Intel i7-10700 Gen10 CPU, 32GB RAM, Geforce GTX 1660 OC 6GB Windows 10 Pro 22H2, 1x 1TB M.2 NVMe, 1 x 2TB M.2 NVMe. Affinity APh, APu, ADe Link to comment Share on other sites More sharing options...
missamae Posted May 4, 2022 Share Posted May 4, 2022 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 Quote Link to comment Share on other sites More sharing options...
paulo Dekkers Posted March 22 Share Posted March 22 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. Quote Link to comment Share on other sites More sharing options...
walt.farrell Posted March 22 Share Posted March 22 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. Quote -- Walt Designer, Photo, and Publisher V1 and V2 at latest retail and beta releases PC: Desktop: Windows 11 Pro, version 23H2, 64GB memory, AMD Ryzen 9 5900 12-Core @ 3.00 GHz, NVIDIA GeForce RTX 3090 Laptop: Windows 11 Pro, version 23H2, 32GB memory, Intel Core i7-10750H @ 2.60GHz, Intel UHD Graphics Comet Lake GT2 and NVIDIA GeForce RTX 3070 Laptop GPU. iPad: iPad Pro M1, 12.9": iPadOS 17.4.1, Apple Pencil 2, Magic Keyboard Mac: 2023 M2 MacBook Air 15", 16GB memory, macOS Sonoma 14.4.1 Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.